Geek Answers Handbook

MDX Does Not Display Latest Data

I need a little help to figure out the problem that I have in MDX. I want to show the latest version for the measurement period, even if the value is NULL. But my code shows me the latest non-empty version.

Example:

My problem

Here is my code: (visible measure in tables is MNY_ENCOURS)

SCOPE([DIM Version].[VCR BK Version].[All]);
    [Measures].[MNY_AA_Total] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[MNY_AA_Total])));
    [Measures].[MNY_ENCOURS] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[MNY_ENCOURS])));
    [Measures].[MNY_Flottement] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[MNY_Flottement])));
    [Measures].[MNY_Montant_Cale] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[MNY_Montant_Cale])));
    [Measures].[MNY_VARIATION_ENCOURS] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[MNY_VARIATION_ENCOURS])));
    [Measures].[V FCT Collecte Count] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[V FCT Collecte Count])));
    [Measures].[CONFI_FLUX] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[CONFI_FLUX])));
    [Measures].[CONFI_STOCKS] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[CONFI_STOCKS])));
    [Measures].[Declarant_Distinct_Count] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[Declarant_Distinct_Count])));
    [Measures].[MNY_FLUX_CALCULE] = tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[MNY_FLUX_CALCULE])));
    [Measures].[PART_FLUX_Trimestriel]=tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[PART_FLUX_Trimestriel])));
    [Measures].[PART_STOCKS]= tail(filter([DIM Version].[VCR BK Version].[All], not isempty([Measures].[PART_STOCKS])));                  
END SCOPE;

Could you help me? thank you

+6
source share
2 answers

Finally, I had it like this:

iif([DIM Version].[VCR BK Version].currentmember is [DIM Version].[VCR BK Version].[All], SUM(TAIL(NonEmptyCrossjoin([DIM Version].[VCR BK Version].MEMBERS,{[DIM Periode].[Hierarchy].CurrentMember})).item(0),[Measures].[MNY_ENCOURS]) ,[Measures].[MNY_ENCOURS]);

0
source

Why not just go from this template for each measure:

...
[Measures].[MNY_ENCOURS] 
  = TAIL(
      FILTER(
        [DIM Version].[VCR BK Version].[All]
      , not isempty([Measures].[MNY_ENCOURS])
    )
...

for the tuple, as for the following:

...
[Measures].[MNY_ENCOURS] 
  = (
      tail([DIM Version].[VCR BK Version].[All].MEMBERS).ITEM(0).ITEM(0)
     ,[Measures].[MNY_ENCOURS]
    )
...
+1
source

More articles:


All Articles