MDX question

[Marioler]

Hello all,

I wonder if someone can help me out with a MDX. I tried with the help of the MDX primer and Wim's MDX site but I failed.

I have a cube with months as columns. Month 04 and 08 have values, all other months are 0. Is there a way with MDX to show the last month with values (08) and all months before that (01-07)?

Capture1.PNG (4.27 KiB) Viewed 7221 times

So as a result the MDX should show month 01-08.

Capture2.PNG (2.74 KiB) Viewed 7221 times

Is there a way to achieve that with MDX?

Regards,
Mario

[paulsimon]

Hi Mario

If the last month with data is random then there is certainly no easy bit of MDX that will do this. However, what is the business logic here? I am guessing that period 08 is your current period and therefore you want to show all periods up to this regardless of whether they have actuals, but don't want to show future periods that cannot possibly have actuals?

If that guess is right then the easiest approach is to have a subset that selects all periods up to and including the current one. You can then quote that on the columns of the MDX select statement as [Dimension].[Subset Name], or if you just want this in a conventional Native View then use that subset on the columns of your view. You can either define that subset using MDX, or just update a static subset to have the right periods in your month end rollover process.Either way you will probably need a reference cube of some sort to hold the current period, if you haven't got one already.

If you check my website www.successcubed.co.uk there is a link to a time dimension generator and related processes. Even if you don't use that, it should give you clues as to how to create YTD consolidations for any period, which then makes it easy to expand out to get the periods up to that point in the year. There are some examples around subsets, although not the full set as that would entail publishing too much in the way of background code and cubes.

Regards

Paul Simon

[PavoGa]

What is the structure of your period dimension? In other words, do the months rollup to quarters/years/etc?

[PavoGa]

This may look like a boat load of ugly, but it will do what you are asking in your op.

This query assumes you have only one level above the periods, say "Total Year".

Code: Select all

PERIODSTODATE( [Manad].[Level000],
    TM1MEMBER( TAIL( FILTER( [Manad].[Total Year].children,
        [cubename].([z.TestRowEntryList].[R01]) > 0), 1).item(0), 0))
 

Where Level000 = the level that "Total Year" is. Level001 = the periods. I hope that is clear. This actually may work with Level000 if you have no parent for the periods.

[declanr]

This may look like a boat load of ugly, but it will do what you are asking in your op.

This query assumes you have only one level above the periods, say "Total Year".

Code: Select all

PERIODSTODATE( [Manad].[Level000],
    TM1MEMBER( TAIL( FILTER( [Manad].[Total Year].children,
        [cubename].([z.TestRowEntryList].[R01]) > 0), 1).item(0), 0))
 

Where Level000 = the level that "Total Year" is. Level001 = the periods. I hope that is clear. This actually may work with Level000 if you have no parent for the periods.

That's a lovely bit of MDX and I think you undersell it by pointing out that it is dependent on levels etc.
The core logic you have come up with is to find the first or last element from a filtered set and converting the single element set to a member.
Once you get to the point of having [Manad].[08] as a member there are dozens of ways to convert it to the range of [Manad].[01]:[Manad].[08]

I would say the combination of TM1Member and Tail (or Head) is the perfect trick. What goes inside and outside of those 2 sections is the trivial bit.

[Wim Gielis]

For those of you reading the topic and the suggested nice (!) MDX formula of PeriodsToDate;
this formula can also be used in dimensions (hierarchies) that are not dealing with Time or Periods.
So Accounts, Cost Centers, Companies, ... though of course it still has to make sense.

For the MDX given, if you do not want to use the }HierarchyProperties cube and a TI process to set level names, an alternative:

Code: Select all

PERIODSTODATE( [Manad].[Total Year].Level, TM1MEMBER( TAIL( 
FILTER( [Manad].[Total Year].children, [cubename].([z.TestRowEntryList].[R01]) > 0), 1).item(0), 0))

whereby "Total Year" is the name of the parent of the individual periods.

[gtonkin]

Have to agree with Declan - great code Ty!

@Alan - a like button would be great in these situations

[Mark RMBC]

This gets a thumbs up from me too.

Just to illustrate what Declan said about the trick being tm1member and Tail, this would work too:

Code: Select all

LASTPERIODS(99, TM1MEMBER( TAIL( FILTER( [Manad].[Total Year].children, NOT(ISEMPTY([cubename].([z.TestRowEntryList].[R01])))), 1).item(0), 0))

But I would certainly go with PavoGa's ingenious suggestion.

regards,

Mark

[Marioler]

Hello everyone!

Thanks so much for all the great input and comments.

Special thanks of course to Ty and Wim.
@Ty: Big thank you, this was exactly what I needed. And I agree with the others - great code.
@Wim: I went with your version (without being dependant on the }HierarchyProperties cube), thanks for the alternative.

Really happy now

Regards,
Mario

[PavoGa]

Hello everyone!

Thanks so much for all the great input and comments.

Special thanks of course to Ty and Wim.
@Ty: Big thank you, this was exactly what I needed. And I agree with the others - great code.
@Wim: I went with your version (without being dependant on the }HierarchyProperties cube), thanks for the alternative.

Really happy now

Regards,
Mario

You're welcome and I would have used Wim's flavor of it as well.

[Wim Gielis]

You're welcome and I would have used Wim's flavor of it as well.

Without yours it would have been 12 nested IF's for me. Or a rule that is then used in the MDX !