topic Re: Rolling values with weeks and periods in App Development

Rolling values with weeks and periods

semgem12 — Fri, 25 May 2018 08:04:06 GMT

Hello, hope someone can help with this please.

I have a table of values which are stored by financial period (not month) and week. Each period has a number of weeks, e.g. period 1 always has 4 weeks, but period 2 always has 5 weeks. The weeks just roll forward, they don’t restart at 1 each time a period starts. There are 12 periods and 52 weeks per year.

E.g.

Load * inline [

Period,Week,Value

1, 1, 25.00

1, 2, 30.00

1, 3, 27.00

1, 4, 23.00

2, 5, 28.00

2, 6, 29.00

2, 7, 22.00

2, 8, 32.00

2, 9, 21.00

3, 10, 19.00

];

I have to show this table as a Qlik Sense table in the same format as above but I need another column that shows the value as a rolling value that restarts at each period, i.e.

Period	Week	Value	Rolling Value	Notes
1	1	25.00	25.00	week 1
1	2	30.00	55.00	week 1+2
1	3	27.00	82.00	week 1+2+3
1	4	23.00	105.00	week 1+2+3+4
2	5	28.00	28.00	new period, so just week 5
2	6	29.00	57.00	week 5+6
2	7	22.00	79.00	week 5+6+7
2	8	32.00	111.00	week 5+6+7+8
2	9	21.00	132.00	week 5+6+7+8+9
3	10	19.00	19.00	new period, so just week 10 etc.

I think I need to use rangesum(above(Value)) in some way but can’t work out how.

Thanks for any help.

Re: Rolling values with weeks and periods

pradosh_thakur — Fri, 25 May 2018 08:18:15 GMT

load *, if(peek(Period)=Period ,Value +peek(Value),Value) as Rolling_value,

;

Load * inline [

Period,Week,Value

1, 1, 25.00

1, 2, 30.00

1, 3, 27.00

1, 4, 23.00

2, 5, 28.00

2, 6, 29.00

2, 7, 22.00

2, 8, 32.00

2, 9, 21.00

3, 10, 19.00

];

Re: Rolling values with weeks and periods

ogautier62 — Fri, 25 May 2018 10:03:41 GMT

Hi,

in the graph

[rolling value] = if(rowno()=1,value,above([rolling value]) + value)

regards

Re: Rolling values with weeks and periods

semgem12 — Wed, 30 May 2018 07:03:50 GMT

Thank you, unfortunately this does not work. It seems to add successive pairs of values,

i.e.

Period	Week	Sum(Value)	sum(Rolling_value)
1	1	25	25	week 1
1	2	30	55	week 1+2
1	3	27	57	week 2+3
1	4	23	50	week 3+4
2	5	28	28	etc.

Re: Rolling values with weeks and periods

devyanshu_gupta — Wed, 30 May 2018 09:20:17 GMT

Hi,

Try this

Load *,

if(Period=peek(Period),peek(Rolling_Value)+Value,Value) as Rolling_Value

Resident Table

Order By Period,Week;

Regards,

Devyanshu

Re: Rolling values with weeks and periods

ogautier62 — Wed, 30 May 2018 09:28:38 GMT

Hi,

just rolling_value , not sum(rolling_value) !