Need last 3 months data in pivot table

[#1]

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.

[sunny_talwar]

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

[#3]

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.

[sunny_talwar]

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

[#5]

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.

[sunny_talwar · Accepted solution]

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;

[#7]

Thanks Sunny for help.