topic Cumulative Sum that doesn't fill future months in QlikView

Cumulative Sum that doesn't fill future months

aliyoung92 — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to create a pivot with a cumulative sum based on current and previous financial year and split between periods.

The previous year works great with the below expression, however the current year fills the future periods with the same value as the last month of data as there is no data yet in the system.

Could someone provide a solution how to stop periods with no data copying what is in the previous period.

I.e. April displays £15m and then all the periods after do also as I believe it is just looking at the previous period and trying to add an additional value.

=rangesum(before(Sum({$<Grouping={"Income"}>}Actual),0,columnNo()))*-1

Thanks

Re: Cumulative Sum that doesn't fill future months

sunny_talwar — Tue, 02 May 2017 10:55:39 GMT

May be this

=If(Sum({$<Grouping={"Income"}>}Actual) > 0, RangeSum(Before(Sum({$<Grouping={"Income"}>} Actual), 0, ColumnNo()))*-1)

Re: Cumulative Sum that doesn't fill future months

beck_bakytbek — Tue, 02 May 2017 11:01:20 GMT

Hi Alistair,

look at this: https://www.youtube.com/watch?v=zIVj4U9QqPQ

i hope that helps

Beck

Re: Cumulative Sum that doesn't fill future months

aliyoung92 — Tue, 02 May 2017 13:55:26 GMT

Thanks Sunny this helped me out.