topic Re: Last 13 months Sales in QlikView

Last 13 months Sales

priyarane — Mon, 16 Mar 2015 11:37:40 GMT

Hi Experts,

I want to have the Sum(Sales) for last 13 months including current month.

My filter name - MonthYear

My script looks like this for Date fields

MonthEnd(Date#(Reporting_Date,'YYYYMM')) as Reporting_Date,
Month(Date#(Reporting_Date,'YYYYMM')) & ' ' & PurgeChar(year(Date#(Reporting_Date,'YYYYMM')),'20') as MonthYear,

You can find my application.

Based on MonthYear selection I should get for last 13 months including current month.

Right now I have Sep14 ony, but tonight I will get total data. So I need to put expression before reload.

Re: Last 13 months Sales

timanshu — Mon, 16 Mar 2015 11:54:55 GMT

Hi Priya,

1) Create a variable. Example vCustomDate = Date(AddMonths(Reporting_Date,-13)).

2) write expression as = Sum({<Reporting_Date='>=$(=vCustomDate)<=Reporting_Date'>}Sales)

This will work for you.

Re: Last 13 months Sales

priyarane — Mon, 16 Mar 2015 12:05:03 GMT

Hi Tim,

I guess in Expression, we have syntax error,

Could you please correct it? I am not able to get it

Re: Last 13 months Sales

MK_QSL — Mon, 16 Mar 2015 13:11:57 GMT

What is the Format of MonthYear and Reporting_Date?

Re: Last 13 months Sales

priyarane — Mon, 16 Mar 2015 14:25:27 GMT

MonthEnd(Date#(Reporting_Date,'YYYYMM')) as Reporting_Date

Re: Last 13 months Sales

MK_QSL — Mon, 16 Mar 2015 14:38:31 GMT

MonthYear?

Re: Last 13 months Sales

jonathandienst — Mon, 16 Mar 2015 14:45:15 GMT

Something like this should do it:

Sum({<Reporting_Date={">=$(=AddMonths(Max(Reporting_Date), -13))"}>} Sales)

HTH

Jonathan