Accumulated Sum - showing only 12 last months in the chart

[thomas_k-r]

Hi

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

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):

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

[Digvijay_Singh]

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;

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

[thomas_k-r]

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

[MK_QSL]

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()))

[MK_QSL · Accepted solution]

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]

Good one!

[sunny_talwar]

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)

[MK_QSL]

Excellent !