How to manipulate Cross-tab Total column based on Condition?

[PRIT AMRIT]

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

[PRIT AMRIT]

SORRY, the cross tab eg is not clear.

Pls find the attached screenshot.