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
You need to do this in one Crosstab. What is your datasource... relational, DMR or PowerCube?
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!
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.
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!
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.
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!
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.
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