Set Analysis - Previous Month Values in Pivot Table

[#1]

Hi,

I seem to have a problem, i've tried searching the forum and applying previous answers to my problem, but I cannot somehow get any of them to work correctly. I am trying to display the Sum Dollar Total of the

I have a variable SelDate which is a user chosen date with the property 'Always One Selected Value'.

I calculate SelDate at the time of script loading deriving it from ToDate:

FormattedDate:
LOAD ToDate,
Date(MonthStart(ToDate), 'MMM YYYY') as SelDate
RESIDENT DataSet;

This sets every item in the dataset to have a date of the 1st of the respective month and year, eg. 01/01/2009, 01/04/2009.

In my pivot table I am trying to use an expression like this, however it does not appear to work:

Sum({1<SelDate={$(=(AddMonths(SelDate,-1)))}>} DollarAmount)

It just returns dashes, any ideas as to what I'm doing wrong?

Thanks in advance

Michael

[#2]

Try adding quotes around the dollar sign expansion.

Sum({1<SelDate={'$(=(AddMonths(SelDate,-1)))'}>} DollarAmount)

I tried using AddMonths on a Date format like yours and it works fine.

[#3]

Hi NMiller,

That doesn't seem to work I still get zero.

Sum(DollarAmount)

works correctly, returning the currently selected month, and every other field in my tables work correctly, just not the date that is contained in SelDate, it is very annoying!

[johnw · Accepted solution]

You need to format the SelDate after you subtract the month. It doesn't preserve your special formatting, and is doing more of a text comparison than a numeric comparison. Also, once you do format it, you're going to have to put it in quotes or QV will probably get confused. So I'm guessing that this would work:

Sum({1<SelDate={"$(=date(AddMonths(SelDate,-1),'MMM YYYY'))"}>} DollarAmount)

[#5]

Works perfectly, thanks John!