topic Re: Set Analysis for looking at data 12 months back from Max(Date) field? in QlikView

Set Analysis for looking at data 12 months back from Max(Date) field?

jblomqvist — Mon, 08 Jun 2015 15:45:53 GMT

Hello there,

Can anyone share an example of how to write Set Analysis expression for looking at data for the last 12 months from Max(Date) field?

The Date field contains the dates and I want to look at the last 12 months data only from the max Date.

E.g. It would be part of Sum(Sales) expression.

Thank you

Re: Set Analysis for looking at data 12 months back from Max(Date) field?

sunny_talwar — Mon, 08 Jun 2015 15:51:38 GMT

May be this:

Sum({<Date = {"$(='>=' & Date(AddYear(Max(Date),-1)))"}>}Sales)

just need to make sure that $(='>=' & Date(AddYear(Max(Date),-1))) has the same format of date as Date field otherwise this won't work.

Best,

Sunny

Re: Set Analysis for looking at data 12 months back from Max(Date) field?

jblomqvist — Mon, 08 Jun 2015 16:08:02 GMT

Hi Sunindia,

Thank you for your reply Currently my Date is in the numeric format.

I am also using this Date field for a calculated dimension, where I am formatting it to =Date(Date,'MMM-YY') format. Do I need to add this formatting to my Max function also?

Re: Set Analysis for looking at data 12 months back from Max(Date) field?

sunny_talwar — Mon, 08 Jun 2015 16:13:39 GMT

Then try this:

Sum({<Date = {">= $(=Num(AddYear(Max(Date),-1)))"}>}Sales)