Accumulation in chart

[qlikplaut]

Hello,

I have a problem and I am running out of ideas   Let me try to describe it in a simple way:

I have a table that looks like this:

week_rap	CES
20	2
20	3
20	2
21	4
21	3
21	3
22	4
22	6
22	4
23	8
23	9
23	0
24	8
24	5
24	5
25	4
25	6
25	3

In a chart, if i use as a dimention week_rap and as an expression sum(CES) this is what I get:

week_rap	sum(CES)
20	7
21	10
22	14
23	17
24	18
25	14

What I need to do is to create a table(or chart) with the dimension week_rap and an expression that will do this:

sum(if(week_rap>week_rap-4 and week_rap<=week_rap,CES)

Is there any way I can create a column that stores sum(CES) for the current week and the previous 3weeks as well?

The accumulate option( from chart properties - expressions) cannot help as I have to divide that column to another one.

Any ideas?

Thank you in advance for your help,

Bogdan

[Nicole-Smith]

An expression like this should work:

RangeSum(above(sum(CES),0,4))

I have also attached a working example.

[qlikplaut]

Is there any way this works in the script? RangeSum(above(sum(CES),0,4)) as CESTotal ? Or is this something that needs to be used in a chart?

[Nicole-Smith]

The expression I gave will only work in a chart.  If you want it done in the script, it will need to be written differently.

[qlikplaut]

I need it to work in the script and create a column with this value.

[agilos_mla · Accepted solution]

Could the following script answer you case?

Michael

Data:

LOAD * INLINE [

    Week, CES

    20, 2

    20, 3

    20, 2

    21, 4

    21, 3

    21, 3

    22, 4

    22, 6

    22, 4

    23, 8

    23, 9

    23, 0

    24, 8

    24, 5

    24, 5

    25, 4

    25, 6

    25, 3

];

Data_Sum:

Load Week,

  SUM_CES,

  rangesum(peek(SUM_CES, -1),Peek(SUM_CES,-2),Peek(SUM_CES, -3)) AS SUM_CES_3_WEEK_BEFORE;

Load

  Week,

  sum(CES) as SUM_CES

Resident Data

GROUP BY Week;

Drop table Data;

[qlikplaut]

I think this should to the trick!

Thank you very much Michael!