topic Last 12 Months in QlikView

Last 12 Months

Mon, 08 Jul 2013 19:01:06 GMT

Hi All,

Need to show last 12 months of data based on existing date filed. How can i achive this ?

Thanks

jagan — Mon, 08 Jul 2013 19:16:10 GMT

Hi,

Try this expression

='QTY= ' & sum({<Trans_Date={'>=$(=MonthStart(Max(Trans_Date), -11))<=$(=Max(Trans_Date))'}>}QTY)

Create variable with vMaxDate = Max(Trans_Date) and then use below expression

='QTY= ' & sum({<Trans_Date={'>=$(=MonthStart(vMaxDate, -11))<=$(=vMaxDate)'}>}QTY)

Hope this helps you.

Regards,

Jagan.

Tue, 09 Jul 2013 18:41:18 GMT

Hi Jagan,

Is it possible to load last 12 months of data while loading the data in the script ?

jagan — Tue, 09 Jul 2013 19:14:43 GMT

Hi,

Use script below to reduce last 12 months in script level

DataTemp:

Data:

LOAD Max(Trans_Date) AS MaxDate

FROM

Last12Months.xlsx

(ooxml, embedded labels, table is Sheet1);

let vMaxDate = Peek('MaxDate');

Data:

LOAD Trans_Date,

QTY

FROM

Last12Months.xlsx

(ooxml, embedded labels, table is Sheet1)

where Trans_Date >= AddMonths($(vMaxDate), -12) AND Trans_Date <= $(vMaxDate);

Regards,

Jagan.