how to sum a field for certain number of rows after each row?

[#1]

Hi,

assume i have 3 fields (date,is_holiday and measure) in may data table.

what i'm tying to do is that summing  the measures for 45 rows after each row . this 45 means, 45 working day which are separated with is_holiday flag.

how can i have this summation for each row in my charts?

example with summation of 3 days after for each day:

date                is_holiday                measure     |     required summation

*****************************************************************************

2016/10/26            0                          10           |       10+20+40

2016/10/27            0                          20           |      20+40+50

2016/10/28            1                          30           |         40+50

2016/10/29            0                          40           |          40+50

2016/10/30            0                          50           |           50

thanks in advance

Saina Mohamadyari

[sunny_talwar]

Not sure I understand your logic here. Would you be able to elaborate a little?

[#3]

i think if i add more rows it would become clear:

date                is_holiday                measure     |     required summation of measure field for each day

**********************************************************************************************************************

2016/10/26            0                          10           |       10+20+40

2016/10/27            0                          20           |      20+40+50

2016/10/28            1                          30           |         0(because it's a holiday)+40+50

2016/10/29            0                          40           |          40+50+60

2016/10/30            0                          50           |          50+60+70

2016/10/31            0                          60           |       60+70+80

2016/11/01            0                          70           |           70+80+90                                    

2016/11/02            0                          80           |           80+90+0(no rows remains)                 

2016/11/03            0                          90           |            90+0+0  (no rows remains)

thanks                               

[benjamins]

There is something strange going on with your logic for row no for the holidays in your example.

In row 1 and 2 you skip the Holiday and instead add another row, in the actual row of the Holiday you set it to '0'.

Anyway. See attached sample.

Notice, that I set the Holiday rows to '0' instead of skipping.

Also, if I understand correctly, you want to add the following 45 days. You should use an iterator for that...

[#5]

i know it's strange!

thanks for your reply,that was very helpful.

[jonathandienst]

Use an expression like:

=RangeSum(Below(Sum({<is_holiday= {0}>} measure, 0, 3)))

or, if you have multiple dimensions:

=RangeSum(Below(TOTAL Sum({<is_holiday= {0}>} measure, 0, 3)))

[#7 · Accepted solution]

i used this expression at the end,thanks from every one!

if(RangeSum(Below(TOTAL Sum(isholiday), 0, 3)),

RangeSum(Below(TOTAL Sum(amount), 0, 3+RangeSum(Below(TOTAL Sum(isholiday), 0, 3)))),

RangeSum(Below(TOTAL Sum({<isholiday = {0}>} amount), 0, 3)))