% of last Month

[leahmarie]

Hey everyone!

So I am writing this report and they want to see the percentage change each month, which seems easy enough, where you would just take the current measure and subtract it from the previous month. But it is a rolling 3 months and MTD so basically subtracting each one from the previous.  For example:::


This is what it hypothetically should look like-
                 Jan      Feb       Mar    Apr
Revenue    12       34         56      78
% Change  0         64%     40%   28%


Any ideas on how to make that happen?:):)

[cognostechie]

What's the source? Relational or OLAP ?

If it's OLAP, you can use this:

Tuple(CurrentMember([Date])) / Tuple(PrevMember(CurrentMember([Date])) - 1

[leahmarie]

Thanks for the reply.  Yes it is OLAP, but for some reason that didn't work.  It could be because of how I have it set up, for example it's a crosstab that looks:


               April                                   May          MTD           YTD     
                Actual PPD Budget PPD
Expense    xx                 xx

% Change   xx                xx

Where May MTD and YTD all have Actual PPD and Budget PPD underneath them, and they are calculated measures that include tuples, not a simple measure.  I also tried just using Expense/prevmember(Expense) which gave me a number but not a correct one.  I think the prevmember is on the right track.  I think it might need to be added to the columns, which is not what the end user wants.

[cognostechie]

You will have to play around with it . I have it working in a report in the same format that you mentioned in your original post.

[leahmarie]

hmm.. I have been playing around with it for a few days.  Everytime I use any kind of previous member it brings up a number so far off anything that it would be and I have tried breaking it up just so it would show the one above it.  I think that maybe my question came off that I couldn't get the previous dates into columns, but actually what I need is that Change % row to work.  pulling currentMember and Prevmember of the dates won't help in calculating that I am afraid.  I am sorry if I sound dumb, I am a newer user to Cognos.  But if my Expense line says 7.4 for April and I used a calculated member that says tuple(prevMember(Expense)) it should bring up 7.4 for May right?

[leahmarie]

Nevermind, I was just confused. I got it to work thank you so much!! I was just confused because my dates come from three different hierarchies and I was trying to use a generic one. Thanks soooooo much again!:)