Cumulative Above RangeSum straight Table

[oscarvictory]

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.

[sunny_talwar · Accepted solution]

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;

[oscarvictory]

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.

[sunny_talwar]

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

[oscarvictory]

That was totally helpful!!

Very grateful from my side.

RGDS.