topic Re: Cumulative Sum on specific rows in QlikView

Cumulative Sum on specific rows

NicholasP — Sat, 16 Nov 2024 01:02:43 GMT

Hi everyone,

this is my first post and I apologize if this has been asked before (actually, I couldn't find any proper solution) or if I'm missing to share some info.

Anyway, my problem is the following, I have a table with some data:

DATA
1
2
3
4
5
6
7
8
9
10

and I would like to cumulate its values based on a "day" criteria. In other words, I'm trying to get this output:

DATA	2 days	3 days	4 days	5 days
1
2	3
3		6
4	10		10
5				15
6	21	21
7
8	36		36
9		45
10	55			55

so, this means that I will need to cumulate starting always from the top of "DATA" (value=1) but skipping rows based on headers.

I hope my problem is clear

Thanks a lot for all your support!

Re: Cumulative Sum on specific rows

Taoufiq_Zarra — Tue, 17 Mar 2020 08:44:12 GMT

Can you clarify the rule?

Re: Cumulative Sum on specific rows

NicholasP — Tue, 17 Mar 2020 09:06:17 GMT

Yes sure.

The idea is to cumulate "DATA" values, always from the top ("DATA" = 1). Each new column represents the frequency of the cumulate action.

For example, under "2days" column it means I have to cumulate data from the top every 2 days. So, it would be:

1day nothing
2day 1+2=3
3day nothing
4day 1+2+3+4=10
and so on...

(there was an error on the table, now it's right)

Re: Cumulative Sum on specific rows

Taoufiq_Zarra — Tue, 17 Mar 2020 09:27:28 GMT

Re: Cumulative Sum on specific rows

tresB — Tue, 17 Mar 2020 09:29:54 GMT

I have never seen a data analytics problem like this so far. But not a problem, we are here to learn/know new things. Coming back to your problem. How far can your data go? How do you decide that you have to stop at Day 5 here? Also could you explain your business case for this?

Re: Cumulative Sum on specific rows

NicholasP — Tue, 17 Mar 2020 09:41:16 GMT

Thanks for your comment.

You got the idea, now my problem is removing the other values and display only values that you see in my table.

I can do it manually as in my example but I've got thousands of rows and they will increase in the future.

Re: Cumulative Sum on specific rows

Taoufiq_Zarra — Tue, 17 Mar 2020 09:45:42 GMT

how to remove them? the rule ?

and 2->5 are fixed ?

Re: Cumulative Sum on specific rows

NicholasP — Tue, 17 Mar 2020 09:55:40 GMT

Actually, it can go over 5. It was just to give an idea of my problem. Let's say this since our thoughts are based on a calendar we are considering weeks (from every day till every 7 days), months (will be our next scope)

My business case is the following:

-values under "DATA" are the request of notifications that we receive (so they are not that linear). Every day new people are asking to get notified.

So what we are trying to understand is how often we should notify them sand since every day there are new members we will need to consider both new and old requests. A the end we will have a big table with all this information that will help us decide.

Hope this help

Thanks a lot

Re: Cumulative Sum on specific rows

tresB — Tue, 17 Mar 2020 09:52:00 GMT

Taoufiq,

The rule part is probably like:

they would appear in rows where they are divisible by the corresponding day number

But your 2->5 query remains to be answered.

Re: Cumulative Sum on specific rows

NicholasP — Tue, 17 Mar 2020 09:58:53 GMT

2 -> 5 are fixed in my example.

But this number could increase if we want to evaluate new options.

Right now we are working on a range that goes from every day till every 7 days (so 1->7)

Re: Cumulative Sum on specific rows

tresB — Tue, 17 Mar 2020 10:03:15 GMT

In the script or chart?

Re: Cumulative Sum on specific rows

NicholasP — Tue, 17 Mar 2020 10:20:21 GMT

It would be great to have it on script 🙂

Otherwise on the chart would be also ok.

Re: Cumulative Sum on specific rows

tresB — Tue, 17 Mar 2020 10:20:55 GMT

A crude script solution could be like:

Load DATA,AccDATA, If(Mod(RowNo(),2)=0, AccDATA) as Day2, If(Mod(RowNo(),3)=0, AccDATA) as Day3, If(Mod(RowNo(),4)=0, AccDATA) as Day4, If(Mod(RowNo(),5)=0, AccDATA) as Day5, If(Mod(RowNo(),6)=0, AccDATA) as Day6, If(Mod(RowNo(),7)=0, AccDATA) as Day7 ; Load DATA, If(RowNo()=1,DATA, RangeSum(Peek('AccDATA'), DATA)) as AccDATA ; Load * Inline [ DATA 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ]; Drop Field AccDATA;

Re: Cumulative Sum on specific rows

NicholasP — Tue, 17 Mar 2020 16:54:17 GMT

Thanks Tresesco, I tried but it works in part.

Indeed, if I add a Date column (to have a vision of what the situation would be each day) I end up with 7 times the same day for each day in my range

Re: Cumulative Sum on specific rows

NicholasP — Mon, 23 Mar 2020 07:31:27 GMT

Thanks to the community for the support!

I will close this topic since there are no more proposals. I will work on Tresesco solution and find a way!

Thanks!

Re: Cumulative Sum on specific rows

qliksus — Tue, 24 Mar 2020 11:56:31 GMT

One more way to do this in frontend is to create 4 expression like the below

Expression:

2nd day = sum({<DATA={'2','4','6','8','10'}>} aggr( RangeSum(above(sum(DATA),0,RowNo())),DATA))

3rd day = sum({<DATA={'3','6','9'}>} aggr( RangeSum(above(sum(DATA),0,RowNo())),DATA))

4th day = sum({<DATA={'4','8'}>} aggr( RangeSum(above(sum(DATA),0,RowNo())),DATA))

5th day = sum({<DATA={'5','10'}>} aggr( RangeSum(above(sum(DATA),0,RowNo())),DATA))

Re: Cumulative Sum on specific rows

NicholasP — Tue, 24 Mar 2020 15:01:38 GMT

Hi Qliksus,

thanks for your reply! This could be interesting but I have tons of rows..