How to calculate Sales for a Promotion Period?

[dewan_abdullah]

Hi community,

I have two tables:

1- Promotion:

Flyer_ID,

FROMDT,

UPTODT,

PRODUCT

2- Sales:

DocNo,

Product,

DocDate,

SalesAmount

the first table has products that are on Promo during a specific period of time, the second table has sales data for both promo and non promo products. My challenge is how to create a flag or find sales for the products that were under promotion for a specific promotion(Flyer_ID).  

I tried to create a table where it checks if the product from sales table had DocDate>= FROMDT and DocDate<=UPTODT, 'in Promo', 'not Promo') as PromoFlag. ( i tried this https://community.qlik.com/t5/App-Development/How-to-calculate-Sales-product-during-promotion-period... )

But I am getting wrong results. Any help would be appreciated.

Best Regards,

Abdulla

[Vegar · Accepted solution]

You could solve this by using intervalmatch().

Take a look at  my script below:

Promotion:
LOAD 'Yes' as inPromotion, * inline [
Flyer_ID, FROMDT,UPTODT,Product
A, 2022-01-01, 2022-01-31, Christmas decoration
B, 2022-10-01, 2022-10-30, Icecream
];

Sales:
LOAD * inline [
DocNo,Product,DocDate,SalesAmount
1,Icecream, 2022-07-15, 2
2, Christmas decoration, 2022-12-20, 50
3, Christmas decoration, 2022-01-15, 45
4, Icecream, 2022-10-02, 1.5
];

LEFT JOIN (Promotion)
IntervalMatch (DocDate, Product)
LOAD
  FROMDT,
  UPTODT,
  Product
Resident Promotion;

[dewan_abdullah]

Thank you Vegar, it worked!