Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

thomas_k-r
New Contributor

Accumulated Sum - showing only 12 last months in the chart

Hi

I would like to have the same result that I have in the table:

13 months.JPG

So the total acc. sum should still be 214. But I would like to see only 12 last months.

So expected results would be (but without slider of course):

13 months 12 rows.JPG

Is there any method to get that result? So anything before 01/02/2014 should be added to 01/02/2014.

Raw example data:

load * inline [

Date, Value

01/01/2014, 10

01/02/2014, 20

01/03/2014, 10

01/04/2014, 24

01/05/2014, 20

01/06/2014, 12

01/07/2014, 15

01/08/2014, 11

01/09/2014, 9

01/10/2014, 19

01/11/2014, 20

01/12/2014, 11

01/01/2015, 21

01/02/2015, 12

];

THANK YOU

1 Solution

Accepted Solutions

Re: Accumulated Sum - showing only 12 last months in the chart

and if you want to show 01/02/2014 also..

then

dimension

=Aggr(IF(Date >= AddMonths(Max(Total {1}Date),-12),Date),Date)

expression

RangeSum(Above(IF(Date < AddMonths(Max(Total {1}Date),-11),SUM(TOTAL {<Date = {"<$(=AddMonths(Max(Total {1}Date),-11))"}>}Value),SUM(Value)),0,RowNo()))

7 Replies
Digvijay_Singh
Honored Contributor III

Re: Accumulated Sum - showing only 12 last months in the chart

Not sure but I think for this accumulation should happen in the script -

Table1:

load * inline [

Date, Value

01/01/2014, 10

01/02/2014, 20

01/03/2014, 10

01/04/2014, 24

01/05/2014, 20

01/06/2014, 12

01/07/2014, 15

01/08/2014, 11

01/09/2014, 9

01/10/2014, 19

01/11/2014, 20

01/12/2014, 11

01/01/2015, 21

01/02/2015, 12

];

Table2:

load *,

if(Recno()=1,Value,Rangesum(Value,peek(AccSum))) as AccSum

Resident Table1;


Drop table Table1;


Capture.PNG

Expression used as - Sum({<Date={">=$(=Addmonths(Max(Date),-12))"}>}AccSum)

thomas_k-r
New Contributor

Re: Accumulated Sum - showing only 12 last months in the chart

that's great,  but is there any way to do it only in the front-end modifying expr without adding new fields?

Re: Accumulated Sum - showing only 12 last months in the chart

Create a Straight Table

Dimension

=Aggr(IF(Date > AddMonths(Max(Total {1}Date),-12),Date),Date)

Expression

RangeSum(Above(IF(Date <= AddMonths(Max(Total {1}Date),-11),SUM(TOTAL {<Date = {"<=$(=AddMonths(Max(Total {1}Date),-11))"}>}Value),SUM(Value)),0,RowNo()))

Re: Accumulated Sum - showing only 12 last months in the chart

and if you want to show 01/02/2014 also..

then

dimension

=Aggr(IF(Date >= AddMonths(Max(Total {1}Date),-12),Date),Date)

expression

RangeSum(Above(IF(Date < AddMonths(Max(Total {1}Date),-11),SUM(TOTAL {<Date = {"<$(=AddMonths(Max(Total {1}Date),-11))"}>}Value),SUM(Value)),0,RowNo()))

Digvijay_Singh
Honored Contributor III

Re: Accumulated Sum - showing only 12 last months in the chart

Good one!

Re: Accumulated Sum - showing only 12 last months in the chart

You can also try this

Dimension

Date

Expression

=RangeSum(Above(Sum({1}Value), 0, RowNo())) * Avg({<Date = {"$(='>=' & Date(Monthstart(Max(Date), -12)) & '<=' & Date(Max(Date)))"}>}1)

Re: Accumulated Sum - showing only 12 last months in the chart

Excellent !

Community Browser