topic Re: Summing all values with a date less than dimension in QlikView

Summing all values with a date less than dimension

Mon, 19 Nov 2012 19:34:39 GMT

A simplified version of what I am trying to accomplish:

Data Sample:

Date, Value

1/1/2012,10

1/2/2012,20

1/3/2012,30

1/4/2012,90

1/5/2012,100

I would like to plot Date on the X Axis. On the Y axis, I would like to plot the Sum of value where Date <= Dimension value. For example: on the date 1/4/2012, i would like to see the value 150. On 1/2/2012, i would like to see 30. If I click on a single date i should still be able to see the correct total (ie if i select and dispplay only 1/4/2012 I should still be able to see the value of 150)

I have tried using TOTAL in my sum expressions, but this is plotting the total across all dimension values.

Please advise. Thanks,

Andrew

Re: Summing all values with a date less than dimension

Gysbert_Wassenaar — Mon, 19 Nov 2012 19:48:36 GMT

Try: rangesum(above(sum(Value),0,RowNo()))

See attached example

Re: Summing all values with a date less than dimension

Mon, 19 Nov 2012 20:01:08 GMT

Try this:

=if(GetSelectedCount(Date)=1,
aggr(rangesum(above(sum({1}Value),0,500)),Date),
rangesum(above(sum(Value), 0, rowno())))

Re: Summing all values with a date less than dimension

Mon, 19 Nov 2012 20:02:13 GMT

Example File.

Re: Summing all values with a date less than dimension

Mon, 19 Nov 2012 20:06:25 GMT

Thanks to you both for the quick replies!