Year-Over-Year in a Cross Tab

[Ravisha]

Hi,

There is a requirement for which I need to show Year-Over-Year History for Shipments in a Cross tab. The Cross tab rows should have Current year, Prior Year and Current year -2 along with Materials (innermost row). Calendar months ( Jan - Dec ) on Column. It's a Power cube reporting. Is there any way to achieve this using dimensional functions without performing union of 3 sub queries....

I tried creating a union of 3 sub queries each of them having slicers for current year, prior year and year-2(relative time categories). But the report performance is very slow :'(.

I would greatly appreciate if anyone can guide me with this.

Thanks.

[MFGF]

If it's a dimensional package from a cube, you shouldn't need multiple queries. You can use the lag() function to get the prior year and two years previous. Months might be a bigger issue - it depends on the structure of your cube. If you have months as a level below years in the same hierarchy you will struggle. If you have months as a separate dimension it's really easy - just drag the month members to your columns area.

Cheers!

MF.

[Ravisha]

Hi Sir,

I thank you so much for your reply. I really appreciate your help.

Yes, I do have Calendar year months as a separate dimension. I am using this level as a Crosstab column in my report which show months from Jan to Dec.

(Sorry if I didn't explain it in a right way) I want to show Current year, Prior year and Current year-2 in a single row and should be dynamically change when we enter into next calendar year(2015). If we use the lag function we can just show only one sibling  member to the number of position which we specify right ...

for example

     Rows                                    columns       

                          Jan   FEB   MAR    APR  ....  DEC
2014 MaterialA
         MaterialB
         MaterialC
     
2013 MaterialA
         MaterialB
         MaterialC

2012 MaterialA
         MaterialB
         MaterialC

[Lynn]

Try this:

lastPeriods ( 3, lastChild( [Time] ) )

[Ravisha]

Thanks again for the logic sir. I appreciate your effort. I used this function but gave me inappropriate results. I have attached the Calendar Year time dimension image which shows the members (years). The green highlighted part in the image is what i want to show in the row of a Cross tab. 

When I applied your logic the result is : 2015, Late Dates , Invalid Dates.

[Ravisha]

Can anyone please assist me with this. I have been working on this for a while and couldn't come up with good results.  :-\ :'(

Thanks in advance

[Francis aka khayman]

lastPeriods ( 3, [2014] )

[MFGF]

The issue you have is in getting hold of the "current" year easily. You could use a filter function on the Year level and employ a macro to do this, perhaps?

First try this:

Add a query calc to rows with this expression:

filter([your year level], caption([your year level]) = #sq(timestampMask($current_timestamp,'yyyy'))#)

Hopefully this will return the current year member - 2014

If so, you can then use this in your lastPeriods() function to get this and the prior two years:

lastPeriods ( 3, filter([your year level], caption([your year level]) = #sq(timestampMask($current_timestamp,'yyyy'))#) )

MF.

[Ravisha]

Hi Sir,

Thank you for the response.

filter([your year level], caption([your year level]) = #sq(timestampMask($current_timestamp,'yyyy'))#)

The above logic gives me the current year (2014) which is really good. But, when I apply LastPeriods to the above logic it is throwing me an error  :( which says

Invalid coercion from 'memberSet' to 'member' for 'filter(members([Affinity Sales Reports v3.G-BW].[Calendar].[Calendar].[Calendar Year]),rolevalue('_memberCaption',[Affinity Sales Reports v3.G-BW].[Calendar].[Calendar].[Calendar Year]) = '2014')' in 'lastPeriods(3,filter(members([Affinity Sales Reports v3.G-BW].[Calendar].[Calendar].[Calendar Year]),rolevalue('_memberCaption',[Affinity Sales Reports v3.G-BW].[Calendar].[Calendar].[Calendar Year]) = '2014'))'.

[Lynn]

The filter function returns a set, but the lastPeriods function is expecting a member. I wonder if wrapping your filter expression in an item function with index of 0 will return the member for the latest year. Then wrap lastPeriods around that. Not entirely sure, but the item function returns a member from a set and since you have only the one year in your set it should do what you want.

[navissar]

The muppet has the right idea, just the execution needs polishing. a filter() function returns a set. a lastPeriods() function expects a member. That's why you're getting the error.
Try this:
Find out the member unique name for [2014] (right click on it and select "Properties", one of the properties is the MUN). MUN is usually something like:
[cube].[dim].[hierarchy].[level]->:[PC].[@MEMBER].[2014].
Then create a calculation, and set it up as follows, using the MUN you found earlier:
#'[cube].[dim].[hierarchy].[level]->:[PC].[@MEMBER].['+ timestampMask($current_timestamp,'yyyy')+']'#
The macro will create a data item which calls a member unique name that is dynamic and based on current_timestamp. Also, this is a member, so lastPeriods should work with that.

I tested this over the Sales and Marketing sample powercube, where the year MUN is formatted like this:
[great_outdoors_sales_en].[Years].[Years].[Year]->:[PC].[@MEMBER].[20130101-20131231]
I built this expression (I used _add_years because the data ends at 2013):
lastPeriods(3,#'[great_outdoors_sales_en].[Years].[Years].[Year]->:[PC].[@MEMBER].['+timestampMask(_add_years($current_timestamp,-1),'yyyy')+'0101-'+timestampMask(_add_years($current_timestamp,-1),'yyyy')+'1231]'#)
and this works great, so there you have it.

[Ravisha]

Hi All,

Thanks a lot everyone for helping me out. :D It worked out well when I wraped the filter function with Item function. Every one in my team will feel happy today :). A lot of people will benefit from your help. Gentlmen, I dont know how to thank you.. :-[.

[MFGF]

The filter function returns a set, but the lastPeriods function is expecting a member. I wonder if wrapping your filter expression in an item function with index of 0 will return the member for the latest year. Then wrap lastPeriods around that. Not entirely sure, but the item function returns a member from a set and since you have only the one year in your set it should do what you want.

Ahhh - I am a muppet! I should have thought of that, of course! Thanks for this Lynn - the missing piece was the item() function

MF.

[Lynn]

I really like Nimrod's approach with the prompt macro. Macros are really powerful and worth spending time to become familiar. Looks more complicated at first, but truly very elegant to use.

And Ravisha....just want to mention that not everyone here qualifies for the label "Gentlemen". I'm not trying to insult the male forum members who I assume are all worthy of the title, but rather I'm simply pointing out that some of us are female  ;D

[BigChris]

And Ravisha....just want to mention that not everyone here qualifies for the label "Gentlemen". I'm not trying to insult the male forum members who I assume are all worthy of the title, but rather I'm simply pointing out that some of us are female  ;D

WHAT??!?!??! There are women types on here? Am I going to have to start typing in my best handwriting? You're going to want me to have a clean keyboard next...

[Lynn]

WHAT??!?!??! There are women types on here? Am I going to have to start typing in my best handwriting? You're going to want me to have a clean keyboard next...

Ha ha! Made me laugh out loud!!

[navissar]

Cheers,Lynn. You're a fine gentleman.

[Ravisha]

Yes i agree. even Nimrod's approach is excellent.   

sorry about that Lynn. I should have know this.  Thanks to all Gentle(Wo)men as well.  ;D

[Lynn]

Cheers,Lynn. You're a fine gentleman.

Yes i agree. even Nimrod's approach is excellent.   

sorry about that Lynn. I should have know this.  Thanks to all Gentle(Wo)men as well.  ;D

Having too much fun on Cognoise today  ;D

[Gaby]

Hi Muppet and Lynn,

I know this is an old discussion but I have the same issue that was reported here, and this will help me to solve it.

In my case I don't have the Months in a separate dimension, so that was the first step in try to modify my cube to produce this report. My issue is that when I tried to create the New Month dimension it is not creating only 12 members. My result is:

01 02 03 04 05 06 07 08 09 10 11 12 01 02 03 04 05 06 07 08 09 10 11 12

For the source of this dimension, I am using the same that is in the Date dimension which in the other data sources. I am building the cube with Cognos Transformer.

Any help will be appreciated.

[MFGF]

Hi Muppet and Lynn,

I know this is an old discussion but I have the same issue that was reported here, and this will help me to solve it.

In my case I don't have the Months in a separate dimension, so that was the first step in try to modify my cube to produce this report. My issue is that when I tried to create the New Month dimension it is not creating only 12 members. My result is:

01 02 03 04 05 06 07 08 09 10 11 12 01 02 03 04 05 06 07 08 09 10 11 12

For the source of this dimension, I am using the same that is in the Date dimension which in the other data sources. I am building the cube with Cognos Transformer.

Any help will be appreciated.

If you look at the category codes for these members in Transformer (in the Category Diagram) I'll wager you are ending up with independent categories for each month for each year. Although they miught have the same caption, they are different members. I think the trick is not to define them from the underlying date column. Add a data source calculation to extract the month value from the date in your data source, then use these calculated values to build your months in the Transformer model.

Cheers!

MF.

[Gaby]

Thanks!!!

That make the trick. Now I need to combine multiple products.

I appreciate your help.