Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
Highlighted
luisccmm
Contributor

Sum sales applying different and unique Date Ranges for each Store

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!!

Labels (2)
3 Replies
y_grynechko
Contributor II

Re: Sum sales applying different and unique Date Ranges for each Store

Hey,

you can try something like this:

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

luisccmm
Contributor

Re: Sum sales applying different and unique Date Ranges for each Store

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

MVP
MVP

Re: Sum sales applying different and unique Date Ranges for each Store

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein