topic Rolling sum problem in QlikView

Rolling sum problem

qlikviewwizard — Sat, 30 Jan 2016 07:38:53 GMT

Hi All,

I want to show rolling 12 months data for each month in a row as shown below.

Used this formula:

Sum({<Year=, Quarter=, Month=, SalesDate={'>=$(=MonthStart(Max(SalesDate), -11))<=$(=Date(Max(SalesDate)))'}>} Sales )

It is showing data for rolling 12 months with for that month instead of summing up.


For 2016 and January Month
	Year	2016
Ename	Month	January
E1		930
E2		1365
E3		1020
E4		1060
E5		1045

For 2015 and December Month
	Year	2015
Ename	Month	December
E1		940
E2		1370
E3		1020
E4		1020
E5		1020

Re: Rolling sum problem

HirisH_V7 — Sat, 30 Jan 2016 08:14:23 GMT

Hi,

Check this,

PFA,

Hirish

Re: Rolling sum problem

qlikviewwizard — Sat, 30 Jan 2016 08:22:51 GMT

Hi Hirish,

I want to show like this:

For January 2016, need to show Total value at the January Column.

Re: Rolling sum problem

HirisH_V7 — Sat, 30 Jan 2016 08:26:06 GMT

Hi,

check this you need totals for whole year or what!

PFA

Re: Rolling sum problem

qlikviewwizard — Sat, 30 Jan 2016 08:58:56 GMT

Hi Folks,

hic Gysbert Wassenaar swuehl Marco Wedel

Any solution,Please.

Re: Rolling sum problem

Gysbert_Wassenaar — Sat, 30 Jan 2016 09:06:24 GMT

Create an AsOf table in the script:

AsOf:

LOAD distinct

SalesDate as ReportDate,

Year(SalesDate) as ReportYear,

Month(SalesDate) as ReportMonth,

AddMonths(SalesDate,1-IterNo()) as SalesDate

RESIDENT

EmpSales

WHILE

IterNo() <= 12

;

Then replace your chart dimensions with ReportYear and ReportMonth.

See this blog post for more information: The As-Of Table

Re: Rolling sum problem

qlikviewwizard — Sat, 30 Jan 2016 09:13:22 GMT

Hi gwassenaar

Perfect. Thanks a lot.

Re: Rolling sum problem

Anonymous — Sat, 30 Jan 2016 09:51:39 GMT

Hi,

PFA

Hope this helps!!