topic Cumulative Above RangeSum straight Table in App Development

Cumulative Above RangeSum straight Table

oscarvictory — Tue, 12 May 2020 14:17:31 GMT

Hi all,

Hope somebody could help me on that. I have this straight Table in Qlik Sense where you can find the Workind days marked as '1', and some items related to each day. What I need is to cummulate sum of items by Working days, so the sum of not Working days will be on the Working ones.

I did try without success:

Aggr(RangeSum(Above(Sum({<WorkingDay= {0}>} Items))), Date)

That's my Table:

That's the desirable outcom:

Many TKS.

Re: Cumulative Above RangeSum straight Table

sunny_talwar — Tue, 12 May 2020 15:24:12 GMT

Try this

=Aggr(If([Working Day] = 1, RangeSum(Above(Sum(Items), 0, RowNo()))), SNo, (Date, (NUMERIC)))

Where SNo is created in the script like this

Table: LOAD * INLINE [ Date, Working Day, Items 01/01/2020, 0, 3 02/01/2020, 1, 2 03/01/2020, 1, 5 04/01/2020, 0, 1 05/01/2020, 0, 0 06/01/2020, 0, 6 07/01/2020, 1, 10 08/01/2020, 1, 15 09/01/2020, 1, 10 10/01/2020, 1, 3 11/01/2020, 0, 0 12/01/2020, 0, 5 13/01/2020, 1, 7 ]; FinalTable: LOAD *, If(Previous([Working Day]) = 1, RangeSum(Peek('SNo'), 1), Alt(Peek('SNo'), 1)) as SNo Resident Table Order By Date; DROP Table Table;

Re: Cumulative Above RangeSum straight Table

oscarvictory — Wed, 13 May 2020 09:19:23 GMT

Hi Sunny, it worked like a charm!!

If you please, I would like a few words about the logical of your solution. I prefer to understand rather than just copy & paste.

RGDS.

Re: Cumulative Above RangeSum straight Table

sunny_talwar — Wed, 13 May 2020 12:48:48 GMT

So, the first step was to create a field which increment if the previous day is a working day or else stays the same from previous day (will go over why we need this later). But to do this, I used this code in the script

If(Previous([Working Day]) = 1, RangeSum(Peek('SNo'), 1), Alt(Peek('SNo'), 1)) as SNo

The reason to create this field is that we are looking for a way to know the accumulation areas

Now, as I get my areas, I can use SNo as my first dimension to use with the RangeSum(Above()) function. The RangeSum(Above()) function, when used without TOTAL, will restart accumulating after the value of second last dimension changes (in this case we only have 2 dimension, so SNo is the second last dimension)

=Aggr(If([Working Day] = 1, RangeSum(Above(Sum(Items), 0, RowNo()))), SNo, (Date, (NUMERIC)))

Note: You will get a different result if you swap the order of dimensions

=Aggr(If([Working Day] = 1, RangeSum(Above(Sum(Items), 0, RowNo()))), (Date, (NUMERIC)), SNo)

So, it is important to know that the order of dimension matters when you use Above() function. The order of dimension will dictate how your virtual chart is sorted and when does it need to restart the accumulation.

I hope this makes sense? Let me know if you need more help on this

Best,
Sunny

Re: Cumulative Above RangeSum straight Table

oscarvictory — Wed, 13 May 2020 13:17:35 GMT

That was totally helpful!!

Very grateful from my side.

RGDS.