Calculate Sum on Date Range

[akmal_ETG]

Hi all,

I have a table of details as below

Date	Name	Sales
01/01/2024	N1	10
01/01/2024	N2	11
02/01/2024	N1	21
02/01/2024	N2	43
03/01/2024	N1	02
04/01/2024	N1	32
04/01/2024	N2	04
05/01/2024	N2	03
06/01/2024	N1	12

Now I want to calculate Sales for next 3 days. Such as total sales for N1 from 1st till 3rd of Jan. As below result table

Date	Name	Sales	Next 3 Days Sales
01/01/2024	N1	10	33
02/01/2024	N1	21	55
03/01/2024	N1	02	32 - As we don't have data for 5th Jan
04/01/2024	N1	32	44
06/01/2024	N1	12	12
01/01/2024	N2	11	54 - As we don't have data for 3rd Jan
02/01/2024	N2	43	47 - As we don't have data for 3rd Jan
04/01/2024	N2	04	07
05/01/2024	N2	03	03

I tried rangesum(below(sum([sales]),0,3)) but it is taking values from current table not date range

[Oleg_Troyansky · Accepted solution]

Hi there,

Yes, the functions Above() and Below() work on the sheet object that you use it in - unless you enclose them in the AGGR() function with the right set of properly sorted Dimensions - something like this:

sum(

     AGGR(

               rangesum(below(sum([sales]),0,3)),

               Name, (Date, (NUMERIC, ASCENDING))

               )

        )

This is in a nutshell. For a more detailed explanation about advanced uses of the AGGR() function and Set Analysis, allow me to invite you and every Qlik app developer to the upcoming session of the Masters Summit for Qlik in Vienna on September 30th. I will be teaching advanced development techniques there, which include problems like this one.