topic sum over dates in QlikView

sum over dates

Mon, 08 Oct 2012 14:29:49 GMT

Hey, I'm struggling to get the right output for a line chart.

The problem is as follows:

Let's say we have an Athlete X, who scored points in some races. Now I want a line chart with his cumulative points over the period of one year for each date.

In a table it works fine as long as I have a Date selected, the expression for this is:

Sum({<Date={">$(=Num(AddYears(Date,-1)))<=$(=Num(Date))"}>}Points)

So when I select a date it sums the points from one year before the selected date until that date. But when I try to plot this in a line chart it doesn't work.

And when I do select a date the line chart just gives me the dates from one year before that date until the selected date with the points for those specific Dates, so it does not accumulate anything.

Is there any way to make this work? After all, qlikview is able to do the calculations (in a straight table), so I would think it should be possible to put them in a line chart.

Thnx in advance,

Pim

Re: sum over dates

Gysbert_Wassenaar — Mon, 08 Oct 2012 14:40:12 GMT

Maybe you need rangesum instead. Something like rangesum(above(Points,0,rowno())

Re: sum over dates

Mon, 08 Oct 2012 14:45:19 GMT

rangesum(above(Points,0,rowno())) gives a total accumulation of all points, so it is a step in the right direction but we're not quite there, is there any way to make it accumulate only for a period of time of one year?

Re: sum over dates

whiteline — Mon, 08 Oct 2012 17:47:04 GMT

In short the set statement {<Date={">$(=Num(AddYears(Date,-1)))<=$(=Num(Date))"} is calculated once for the entire chart. You could see it in a header of straight table if you don't choose the lable for it.

There're two approaches (to do it dynamicaly):

1) use interrecord aggregations as mentioned above (read the help about parameters of above() function)

2) create 'accumulation calendar' (for example http://community.qlik.com/message/88613#88613)