Different Crosstabs in Report Studio

[kusum29]

Hi,
  I have an issue in performing a calculation from 2 different crosstabs.
How do I deal with it?
For eg: In the first cross tab, I have all the data for the current month, say 200711. In the second cross tab, I have all the data for the previous month, 200710. Now, I need the difference between the total of 200711 and 200710. Since both are in 2 different crosstabs, I'm not sure how to go ahead with the calculation. Can someone please help me?

Thanks
K

[rockytopmark]

You need to do this in one Crosstab.  What is your datasource... relational, DMR or PowerCube?

[kusum29]

Datasource is Powercube.
Actually, the requirement is, the data for October should fall under November so that we can do the calculation. But then, when we had tried doing this in one Crosstab, October's data is falling on the left cell.
For eg:
When we do in one cross tab, this is how it looks -

Curr Mnth        200709              200710               200711
Total          Data of 200709   Data of 200710     Data of 200711
Prev Mnth       
Total          Data of 200709   Data of 20010       Blank


Actually, we need this to look as follows to do the calculation

Curr Mnth        200709              200710               200711
Total          Data of 200709   Data of 200710     Data of 200711
Prev Mnth       
Total          Data of 200708   Data of 200709     Data of 20010 
__________________________________________________________
Calculation    200709-200708  200710-200709    200711-200710


That is the reason we chose 2 cross tabs to make it look like above. But now, we are stuck with the calculation part. Please help!!!   

Thanks Much! 

[MFGF]

Hi,

You can do this fairly easily in one crosstab by using a couple of dimensional functions.  Try the following:

Create a new crosstab.
Drag your measure value in as the default measure.
Drag a Query Calculation into the columns area and call it 'Last 3 Months'.  Use the expression:
lastPeriods(3,closingPeriod([Your month level]))

Drag a Query Calculation into the rows area and call it 'Current Month Total'.  Use the expression:
tuple(currentMember([Your time hierarchy]),[Your measure value])

Drag a Query Calculation into the rows area below Current Month Total and call it 'Prev Month Total'.  Use the expression:
tuple(lag(currentMember([Your time hierarchy]),1),[Your measure value])

Finally, drag a Query Calculation into the rows area below Prev Month Total and call it 'Difference'.  Use the expression:
[Current Month Total] - [Prev Month Total]

This should give you the result you have described, using one crosstab.

Best regards,

MF.

[kusum29]

Hi,
  We tried to do in one crosstab but, we are getting #!Error
in place of the actual value in the 3 rd row.

PMPM CVRG                        2006/May  2006/Jun  2006/Jul 2006/Aug
Renewal         Curr month total       $522.36     $260.02   $278.46   $356.74
Prior Renewal  Prev month            #!Error       #!Error   #!Error   #!Error

We tried to change the solve order property from '0' to '1','2','3' but didn't work.  :(

Any help is much appreciated.

Thank you!

[MFGF]

Hi,

Did you use the steps I provided?  They worked fine for me.  Can you check the syntax of your query calculations to make sure you have things defined correctly?

MF.

[kusum29]

Yes. We used the same syntax that you provided. It's working fine for the current month but it's not working for the previous month. We are getting Error! instead of values. We are thinking the lag is not working fine for the previous month. Can you please suggest what to do?

Thanks Much!

[MFGF]

Hi,

Are you by any chance on an older build of Cognos 8?  I remember seeing these kinds of issues on 8.1 and 8.1MR1, but 8.1MR2 improved things, and 8.2 seems even better.

You could try using the prevMember function instead of lag 1 - ie

tuple(prevMember(currentMember([Your time hierarchy])),[Your measure value])

Does this make any difference?

Regards,

MF.

[kusum29]

Hi,
   Thank you very much for your help. Your solution worked. First time when we got a problem because we had 18 months and the lag function was not working for the previous months. Next time when we tried, we put 24 months and it worked fine.
Thank you so much once again.

Thanks and Regards
K