Hi all,
Can you pls help me in achieving the below requirement?
I have a cross-tab which looks like below now.
Q1 Q2 YTD
----------------- --------------- ---------
Dec Jan Feb TOTAL Mar Apr May TOTAL
X Actual 10 20 30 60 0 0 0 0 60
Budget 20 50 60 140 50 30 40 120 260
Variance -50% -60% -50% -57% -- -- -- -- -76%
Y Actual 15 25 30 70 0 0 0 0 70
Budget 30 40 80 150 10 30 60 100 250
Variance (act-bud)/bud |
The Budget is already defined for the whole fiscal year in the DB. As you can see above for Q2 the Actual is 0 but the budget is still appearing for Mar, Apr, May. Which is fine.
But, the User wants the 'TOTAL' column for Budget to be manipulated. i.e.
"Q1 Budget QTD" means:
If current month is Dec, "QTD"="Dec"
If current month is Jan, "QTD"="Dec"+"Jan"
If current month is Feb, "QTD"="Dec"+"Jan"+"Feb"
Q2 Budget QTD" means:
If current month is Mar, "QTD"="Mar"
If current month is Apr, "QTD"="Mar"+"Apr"
If current month is May, "QTD"="Mar"+"Apr"+"May"
As per the above logic,
Current Month(Feb), 'TOTAL' column for Q2 for Budget should display 0 not 120
Next Month (Mar), 'TOTAL' column for Q2 for Budget should display 50(If current month is Mar, "QTD"="Mar")
When Month (Apr),'TOTAL' column for Q2 for Budget should display 80 ( If current month is Apr, "QTD"="Mar"+"Apr")
:
:
Similarly, the logic for YTD 'TOTAL' column for Budget row
If current month is Dec, "YTD"="Dec"
If current month is Jan, "YTD"="Dec"+"Jan"
If current month is Feb, "YTD"="Dec"+"Jan"+"Feb"
If current month is Mar, "YTD"="Dec"+"Jan"+"Feb"+"Mar"
If current month is Apr, "YTD"="Dec"+"Jan"+"Feb"+"Mar"+"Apr"
If current month is May, ...
Then, now 'YTD' column Budget should display 140 not 260 ( If current month is Feb, "YTD"="Dec"+"Jan"+"Feb")
Next Month (Mar), 'YTD' column for Budget should display 190 (If current month is Mar, "YTD"="Dec"+"Jan"+"Feb"+"Mar")
When Month (Apr),'YTD' column for Budget should display 220
The
Variance for
this month YTD should be (60-140)/140=-57% not -76%
next Month (Mar), YTD should be (60-190)/190
Month (Apr), YTD should be (60-220)/220
To achieve the above requirement, i have been looking at different options but not succeeded yet.
When you apply the 'Aggregate'=Total in a column Cognos will by default create an item with the below expression:
total(currentMeasure within detail [Fiscal Month Name])
I then tried to manipulate it by changing the expression to, for testing
total(currentMeasure within set filter ([Fiscal Month Name],[Fiscal Month Name] in('Dec'))) -- I can able to see only the DEC data under the TOTAL column for QTD.
But when I am trying to write a case statement or if/else it's not working and making the report blank....
Could anybody please tell me where I am going wrong or the approach is wrong?
Would really appreciate your help on this.
Thanks
Prit
SORRY, the cross tab eg is not clear.
Pls find the attached screenshot.