TM1 Forum

Hi, Guys

I have two dimensions, say year and month in the cube. I just want a specific period of time of year and month such as from Feb 2013 to July 2015. How can I filter that? Any workable MDX?

Any idea?

macsir wrote:Any idea?

It's only been a few hours since the original post; give folk a chance!

macsir wrote:Hi, Guys

I have two dimensions, say year and month in the cube. I just want a specific period of time of year and month such as from Feb 2013 to July 2015. How can I filter that? Any workable MDX?

With 2 period dimensions this isn't possible in a cubeviewer at least not by MDX.
Each dimension can only have 1 list of elements; if year was a title dimension you could have MDX in the month to pull back the relevant periods but MDX can only retrieve 1 subset of elements and that same subset would apply to every element in the year dimension if the 2 were nested together.

You could obviously alter the design to have 1 time dimension or depending what the purpose/other data is you could utilise suppression (but that sounds unlikely to be a worked solution) or of course take it out to a websheet/excel sheet and do whatever you want with it.

declanr wrote:
It's only been a few hours since the original post; give folk a chance!

I have been in a little bit hurry coz I think you guys are always quick to help.

declanr wrote:
With 2 period dimensions this isn't possible in a cubeviewer at least not by MDX.
Each dimension can only have 1 list of elements; if year was a title dimension you could have MDX in the month to pull back the relevant periods but MDX can only retrieve 1 subset of elements and that same subset would apply to every element in the year dimension if the 2 were nested together.

You could obviously alter the design to have 1 time dimension or depending what the purpose/other data is you could utilise suppression (but that sounds unlikely to be a worked solution) or of course take it out to a websheet/excel sheet and do whatever you want with it.

Thanks for the confirmation. Then I have to merge them into one dimension to get this done.

logically speaking, it is not possible but I just need someone to confirm it.

macsir wrote:Hi, Guys

I have two dimensions, say year and month in the cube. I just want a specific period of time of year and month such as from Feb 2013 to July 2015. How can I filter that? Any workable MDX?

It would be helpful if you actually provided a more concrete example or a screenshot of what you are trying to achieve. Prima facie no native TM1 UI supports asymmetric member selection on rows in a cross-tab. However there are many possible ways using active forms to be able to solve this presentation problem without resorting to redesigning the reporting cube to combine the time dimensions into a single Year-Month dimension.
E.g.
- 2 (or more) active form sections on adjoining rows in the sheet. One active form section per year with a different set of months per year.
- Using a string measure to drive zero suppression on a hidden column
- An auxiliary helper cube with a single time dimension to drive the active form on a hidden column

macsir wrote:logically speaking, it is not possible but I just need someone to confirm it.

An awful lot is possible using active forms plus some creativity. I wouldn't say it isn't possible. Not with the cube viewer and not with an unadulterated "slice out" active form, but certainly it's possible.

Hi,
What I want to achieve is that when user select start time and end time (e.g. 2011 Dec to 2013 Feb), activeform can get this total expense from one DBRW function with Year and Month dimensions separated. There might be some users running this report at same time. Anyone has specific idea?

macsir wrote:Hi,
What I want to achieve is that when user select start time and end time (e.g. 2011 Dec to 2013 Feb), activeform can get this total expense from one DBRW function with Year and Month dimensions separated. There might be some users running this report at same time. Anyone has specific idea?

I already gave you several ideas that would work. IMO for what you want to do a small auxiliary cube with a single time dimension to drive the row selection would be the easiest to implement. Doesn't matter if there would be 100s of users using the report at once as there's no need to store the user from/to month selection in a cube as you can set the months to display using simple MDX {[YearMonth].[yyyymm]:[yyyymm]}. Just use a time dimension with indexing in time order and no levels for the purpose. Like Declan said "take it out to a spreadsheet/websheet and do whatever you want with it."

This isn't hard to do. I think any more hints and it's bordering on doing your work for you.

Hi, lotsaram

Yes, I have already tried. I have created a time dimension with year and month and using its attribute cube as based to get values from the source cube. But it looks like it can't get values in second DBRW. E.g. it should show values on 2013 02 row.

Actually, after turning on Auto Calculation in excel and rebuilding the sheet, it is showing Key Error but all formulas are in its own right dimension positions.

You can't have DBRW formulas that reference values from other DBRW formulas. This is pretty common knowledge. Change the cells in column C to DBR.

I tried it as well.
All in Key Error.

Okay, DBR function is the first one to execute in Activeform so any formula inside it won't run untill itself finishes. That's why it is showing error on DBR. I am in the loop now...

macsir wrote:Hi, lotsaram
Yes, I have already tried. I have created a time dimension with year and month and using its attribute cube as based to get values from the source cube. But it looks like it can't get values in second DBRW. E.g. it should show values on 2013 02 row.

I have no idea why you are doing what you're doing. You already have the YearMonth elements in column B, why on earth pull the value converted to numeric with a DBRW in column C and then convert that back to text and split it? Frankly that's plain dumb, just use a LEFT, RIGHT or MID formula on column B in columns C & D to get your year and months and the job is finished.

Oh, how silly of me! Thanks for pointing that out, lotsaram.
Yeah, you are right but the root cause is that I haven't pointed elements into right dimensions which I figured out later.