topic Re: Problems displaying Previous Month from User's Selection in QlikView

Problems displaying Previous Month from User's Selection

Tue, 01 Nov 2011 03:26:28 GMT

I am trying to write an expression in the pivot table which will show the total amount of the previous month from a user's selection. ie user chooses June, I want it to show May. It must also filter on Type as Actual. I have tried NUMEROUS expressions - you will see in the definition window as I have left them all in there just commented out. I have also tried as a Previous Month as a variable. I read a few articles as well about "as of" tables but couldn't see how to do this for what I need.

I have attached the file for you and any and all help is much appreciated as I've spent a couple of days on this now and I know this should be fairly simple!!

FYI - I think there might be something special about my Month field as on another formula I used I had to define Month format "MMM YY" in the expression. I think this is probably what's bringing me undone today.

Cheers,

PS - I have since created a new field for Previous Month which is correct however I cannot work out how to override the user's Month selection and replace / filter with Prev Month.

HELP PLEASE!!!

Problems displaying Previous Month from User's Selection

kaushiknsolanki — Tue, 01 Nov 2011 05:43:29 GMT

Hi,

Try this expression for your actual.

SUM({$ < Month = {'$(=addmonths(max(Month),-1))'},Type = {"Actual"}>} Amount)

This will give you the data for the last month, say for example if user selects Dec 11 then data shown will be of Nov 11.

If no month is selected it will show data for the month prior to maximum month.

Regards,

Kaushik Solanki

Problems displaying Previous Month from User's Selection

Tue, 01 Nov 2011 05:52:40 GMT

Hi Kaushik

Thanks for your reply. This did not work - it returned $0s. Any other suggestions?

Problems displaying Previous Month from User's Selection

kaushiknsolanki — Tue, 01 Nov 2011 05:57:25 GMT

Hi,

Yes i know that, its because you dont have data for the month of nov 11 and for the actual.

select some different month and check.

Regards,

Kaushik Solanki

Problems displaying Previous Month from User's Selection

Tue, 01 Nov 2011 06:00:24 GMT

No - doesn't work for any month.

Cheers,

Re: Problems displaying Previous Month from User's Selection

kaushiknsolanki — Tue, 01 Nov 2011 06:15:47 GMT

HI,

Have a look at the attched sample file.

Regards,

Kaushik Solanki

Problems displaying Previous Month from User's Selection

Tue, 01 Nov 2011 06:42:49 GMT

BRILLIANT!!!!! So it was a problem with my document settings.

Thanks so much!!

Problems displaying Previous Month from User's Selection

kaushiknsolanki — Tue, 01 Nov 2011 06:45:08 GMT

Hi,

Glad that my suggestion worked.

Regards,

Kaushik Solanki