topic Set Analysis - Previous Month Values in Pivot Table in QlikView

Set Analysis - Previous Month Values in Pivot Table

Wed, 30 Sep 2009 23:32:06 GMT

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

Set Analysis - Previous Month Values in Pivot Table

Wed, 30 Sep 2009 23:46:21 GMT

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.

Set Analysis - Previous Month Values in Pivot Table

Thu, 01 Oct 2009 15:43:37 GMT

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!

Set Analysis - Previous Month Values in Pivot Table

johnw — Fri, 02 Oct 2009 01:07:20 GMT

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)

Set Analysis - Previous Month Values in Pivot Table

Fri, 02 Oct 2009 19:31:47 GMT

Works perfectly, thanks John!