topic Re: Accumulation in chart in QlikView

Accumulation in chart

qlikplaut — Wed, 05 Feb 2014 19:09:35 GMT

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

Re: Accumulation in chart

Nicole-Smith — Wed, 05 Feb 2014 19:16:52 GMT

An expression like this should work:

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

I have also attached a working example.

Re: Accumulation in chart

qlikplaut — Wed, 05 Feb 2014 19:35:07 GMT

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?

Re: Accumulation in chart

Nicole-Smith — Wed, 05 Feb 2014 19:39:34 GMT

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.

Re: Accumulation in chart

qlikplaut — Wed, 05 Feb 2014 19:44:15 GMT

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

Re: Accumulation in chart

agilos_mla — Wed, 05 Feb 2014 19:56:34 GMT

Could the following script answer you case?

Michael

Data:

LOAD * INLINE [

Week, CES

20, 2

20, 3

20, 2

21, 4

21, 3

22, 4

22, 6

22, 4

23, 8

23, 9

23, 0

24, 8

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;

Re: Accumulation in chart

qlikplaut — Wed, 05 Feb 2014 20:11:46 GMT

I think this should to the trick!

Thank you very much Michael!