Sum sales applying different and unique Date Ranges for each Store

[luisccmm]

Hi,

I need to SUM Sales for each store acording to the specific  and unique date_range for each store.

In the a Qlik Sense app there are two tables as follow.

NOTE: Time format is DD/MM/YYYY

SALES_TABLE:

Store ID     Sales           Date

1                       100           01/01/2019

2                       120          02/01/2019

.....

DATE_RANGE_TABLE:

Store ID       Start_Date      End_Date

1                     01/01/2019       31/01/2019

2                      01/02/2019       17/02/2019

....                   .....                          ....

100              01/03/2019           14/04/2019

I need to SUM Sales for each store acording to the specific and unique date_range on the DATE_RANGE_TABLE  (Start_Date to End_Date) for each store.

So For example:

EXPECTED FUNCTION 

If I sum sales I will have the Sales for Store 1 from 01/01/2019 to 31/01/2019 and for store 2 from 01/02/2019 to 17/02/2019, and the same thing for the other 98 stores  according to the date range that appears on the  DATE_RANGE_TABLE

How I can accomplish that?

Thanks in advance!!

[y_grynechko]

Hey,

you can try something like this:

Sum({<Date={"$(= '>=' & Start_Date & '<=' & End_Date)"}>}Sales)

[luisccmm]

Thanks for your answer,

I am expecting a function that will work as Aggr, or in one celd will give me the Total.

Thanks in advance

[jonathandienst]

If you do this in the load script, you could use the extended interval match (with the store ID as the extra key) to set a flag for when the transaction is in range for that store.

If you need multiple periods, then just extend the interval max table to include those periods, and use a period number instead of  simple flag.