topic How to calculate Sales for a Promotion Period? in App Development

How to calculate Sales for a Promotion Period?

dewan_abdullah — Sun, 16 Oct 2022 12:09:19 GMT

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-only/td-p/1753184 )

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

Best Regards,

Abdulla

Re: How to calculate Sales for a Promotion Period?

Vegar — Sun, 16 Oct 2022 15:52:16 GMT

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;

Re: How to calculate Sales for a Promotion Period?

dewan_abdullah — Mon, 17 Oct 2022 09:03:01 GMT

Thank you Vegar, it worked!