topic Need help with RangeSum in QlikView

Need help with RangeSum

Mon, 31 Aug 2015 11:16:48 GMT

Let me attempt to state my problem to the best of my ability.

I have leavers by month Jan (2); Feb (1); Mar (3); Apr (None); May (4); Jun (2) and Jul (None). I would like to calculate YTD Leavers to get the following results:

Jan (2); Feb (3); Mar (6); Apr (6); May (10); Jun (12) and Jul (12)

Expression: Aggr(RangeSum(SUM({<CalYear={'$(=vCurrentYear)'}>}Leavers),

ABOVE(SUM({<CalYear={'$(=vCurrentYear)'}>}Leavers), 1, RowNo())), CalMonth)

"CalMonth" is the dimension used.

The challenge is with the month Apr and Jul where no one has left. The RangeSum expression is correct but does not reflect any value for these two months. Thereby I am unable to use the YTD Leaver count and further compute.

Re: Need help with RangeSum

swuehl — Mon, 31 Aug 2015 11:20:16 GMT

What is the context you are using the rangesum expression? A chart with dimension CalMonth?

Then try:

=Rangesum( Above( SUM({<CalYear={'$(=vCurrentYear)'}>}Leavers), 0,Rowno() ))

Re: Need help with RangeSum

Mon, 31 Aug 2015 11:32:00 GMT

Yes, I am using it in a chart. The suggested solution did not work.

Re: Need help with RangeSum

swuehl — Mon, 31 Aug 2015 11:36:44 GMT

A chart with more dimensions than 1 (CalMonth)? Could you post a small sample QVW?

Re: Need help with RangeSum

sasiparupudi1 — Mon, 31 Aug 2015 13:18:48 GMT

is your calmonth sorted properly? may be you should use a numeric month so that the above function returns the correct results..

hth

Sasi

Re: Need help with RangeSum

Tue, 01 Sep 2015 04:31:02 GMT

Sorry for the delayed response. I have attached a sample QVW with a note on my expectation.