topic Need last 3 months data in pivot table in QlikView

Need last 3 months data in pivot table

Wed, 25 Nov 2020 16:16:04 GMT

HI All,

I am new to qlikview and working on one of the dashboard where pivot table is involved.I need to show last three months net sales in table as a column and i have below expression.

=Sum({<[Calendar Year/Month (Key)]={">$(vMonthprev) <= $(vMonthprev1)"}>}[NET SALES])

I have Month and year in list box.

Month is in below format

Jan , Feb , Mar ....

year is in below format

2014 , 2015 , 2016 ,2017

I have cal month as a field apart from year and month. Somehow this expression is giving eme 0 value.

I have used vMonthprev = (Date(AddMonths(Date#([Calendar Year/Month],'MMM YYYY'),-1),'MMM YYYY')) variable to get previous month

vMonthprev = (Date(AddMonths(Date#([Calendar Year/Month],'MMM YYYY'),0),'MMM YYYY')) variable to show dynamically selected month and year.

Please guide me if i am doing any mistake while writing expression.

I don't have calmonth in list box but Month and year separately in different list boxes.

I have attached image for reference.please guide me for my query.

Re: Need last 3 months data in pivot table

sunny_talwar — Fri, 27 Jan 2017 18:36:01 GMT

Is [Calendar Year/Month (Key)] read as date field by QlikView?

Get the Dates Right

Why don’t my dates work?

Dates in Set Analysis

Re: Need last 3 months data in pivot table

Fri, 27 Jan 2017 18:56:59 GMT

HI Sunny,

Thanks for reply.I am not using any date field in my dashboard.

Highlighted image in green is from Calmonth and orange one is showing value of variable for month selected through below expression

(Date(AddMonths(Date#([Calendar Year/Month],'MMM YYYY'),0),'MMM YYYY'))

Is it really necessary to have date in dashboard?Please guide me.

Re: Need last 3 months data in pivot table

sunny_talwar — Fri, 27 Jan 2017 19:08:35 GMT

When using set analysis on dates, its always a good idea to have them in dual format because then you can do ranges, you can sort them correctly and so on and so forth. I would def. encourage you to help QlikView read them as date using Date#() or TimeStamp#() function and then your life would be so much easier with the date range you are looking to get

Re: Need last 3 months data in pivot table

Mon, 30 Jan 2017 11:11:42 GMT

Hi Sunny,

i have attached last 3 months sales qvw file along with excel data.Please let me know if i am missing anything in terms of format.

Thanks for your help.

Re: Need last 3 months data in pivot table

sunny_talwar — Mon, 30 Jan 2017 13:19:12 GMT

If I were you, I would handle your dates like this in the script:

Data1:

LOAD Customer,

Date(MonthStart(Date#([CalMonth (Key)], 'YYYYMM')), 'YYYYMM') as [CalMonth (Key)],

Date(MonthStart(Date#(Calmonth, 'MMM YYYY')), 'MMM YYYY') as Calmonth,

Month(Date#(Calmonth, 'MMM YYYY')) as Month,

Sales

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD *,

Year([CalMonth (Key)]) as YEAR,

Num(Month([CalMonth (Key)])) as MONTH

Resident Data1;

DROP Table Data1;

Re: Need last 3 months data in pivot table

Wed, 01 Feb 2017 18:10:46 GMT

Thanks Sunny for help.