topic Last 12 Months Sales in QlikView

Last 12 Months Sales

manideep78 — Tue, 07 Apr 2015 04:18:56 GMT

Dear Community,

I have a date column which is in number format. I want to display the sales of last 12 months including this month. April-15, Mar-15,.., May-14.

My dimension would be say 'Item'.

Note: I want that date expression (which filters the particular month like april-15, mar-15,.....) to be in a variable so that, I can easily reduce one month by subtracting any value from that variable in the set expression. so that I need not write the date expression again and again in all the 12 months sales expressions.

Please suggest any better way of doing it.

Thanks in advance

Regards,

Manideep

Re: Last 12 Months Sales

Tue, 07 Apr 2015 04:27:37 GMT

hi,

use this expression. put your date as place of TRANDATE(numeric field is fine)

vMaxDate1=Date(Max(TRANDATE))

vRollingDate1=AddMonths(Max([TRANDATE]),-11)

(sum({$<FinYear= ,FinMonth=,TRANDATE={"<=$(vMaxDate1) >=$(vRollingDate1)"}>} Amount))

Regards

vimlesh

Re: Last 12 Months Sales

Tue, 07 Apr 2015 06:15:40 GMT

Please find the attached qvw file

Re: Last 12 Months Sales

PrashantSangle — Tue, 07 Apr 2015 06:24:36 GMT

Hi,

Create MonthNAme field in Script like

MonthName(datefield) as MonthName,

then in expression write below set analysis

sum({<dateField={">=$(=date(addmonths(today(),-11)))<=$(=date(today()))"}>}Sales)

or write

max(date) instead of today() if you want on selection basis.

Regards

Re: Last 12 Months Sales

manideep78 — Tue, 07 Apr 2015 06:28:48 GMT

I could not take the max date, since my date field will have maximum date upto 2022 years.

Re: Last 12 Months Sales

manideep78 — Tue, 07 Apr 2015 06:31:56 GMT

I need every month sales from this month to last 12 months. not the aggregated sum of all the last 12 months sales.

Re: Last 12 Months Sales

PrashantSangle — Tue, 07 Apr 2015 06:33:37 GMT

Hi,

Add MonthName as your dimension.

It will work.

Regards

Re: Last 12 Months Sales

manideep78 — Tue, 07 Apr 2015 06:35:01 GMT

My dimension is Item and I need sales for every month starting from this month to last 12 months and not aggregated sum for all the last 12 months

Re: Last 12 Months Sales

manideep78 — Tue, 07 Apr 2015 06:36:58 GMT

I dont want to take monthname as dimension. I need to control it all in the expressions.

Re: Last 12 Months Sales

Tue, 07 Apr 2015 06:41:36 GMT

hi,

vMaxDate1=Date(Max(TRANDATE))

vRollingDate1=AddMonths(Max([TRANDATE]),-11)

if you will select 2022 then it will give result 2022 to 2021 .because you are making variable with your date.

don't use today for maxdate.

this will work what you want.

or send a sample file

Regards

Vimlesh

Re: Last 12 Months Sales

manideep78 — Tue, 07 Apr 2015 06:50:05 GMT

without any selection I want 12 months from 2015 to 2014 thought the max year is 2022. if current year is 2016 then 2016 to 2016 like that. the default year should be current year.

Re: Last 12 Months Sales

Tue, 07 Apr 2015 06:51:53 GMT

hi,

then you fix the date in variable.

Regards

Vimlesh