How to calculate Sales product during promotion period only ?

[StephaneP]

Hi,

I need some helps 😉

I have a master calendar on my sale Date and some promotional campaigns with fields :
- Name
- Start Date
- End Date
- Product key

If I select a campaign name, my products are filtered and I must select the right Sales period to calculate the Sales for the campaign.

During a promotional campaign, the product can be sold only with his promotional price.

I want to create a measure to sum the sales for promotions only. So if it exists a campaign at the Sale Date  between Start Date and End date i must sum the quantity

I don't find how to do that.

Best regards,
Stephane

[Steven_Haught]

@StephaneP 

Would it be possible to see a sample of the data? 

[edwin]

the problem is when you select a date, only products with START DATE on the selected SALE date will be filtered for.  on the next day, the prod will not be selected anymore even if the end date hasnt passed.

what you need to do is associate your calendar to both START and END date so that selecting a single SALE date filters for any product where the SALE date is within the START and END date RANGE.

in the table with start and end date, add the following field:
date(START_DATE,'MM/DD/YYYY') & '|' & date(END_DATE,'MM/DD/YYYY') as %DateKey

create a new table:

noconcatenate
tmp:
load distinct %DateKey, START_DATE, END_DATE  //youll need start and end 
                                        //date only for testing sale date
resident YOURDATATABLE;

inner join (tmp)  //this assumes SALE DATE is unique in calendar table
load SALE_DATE
resident YOURCALENDARTABLE;

noconcatenate
Bridge:
load %DateKey, SALE_DATE
resident tmp
where SALE_DATE >=START_DATE and SALE_DATE<=END_DATE;

drop table tmp;

when a SALE DATE is selected, all products will be selected if SALE DATE falls in between START and END date.

so you just do a count or sum and ot worry about start and end dates

[StephaneP]

Thank you for your answer.

---

@edwin wrote:

when a SALE DATE is selected, all products will be selected if SALE DATE falls in between START and END date.

---

It's not the behaviour i want. If i select a sale date, i want to have all my sales for the day but i want to be able to compare the global sales vs the sales of the products in promotion.

The October 15th i have sales 10 Product A and 5 Products B

I have a campaign between 1st  October and 31th October for product B

If i select the October 15th the global Sales are 15. The promotion Sales must be 5.

It's possible with a formula/set analysis ? Perhaps the loading script approach it's will be a better approach to tag sales 

Best regards,

Stephane

[edwin]

If i understand it correctly, you actually want for each Product and SaleDate, you want to see the total sales separated as Promotional and non-Promotional, so for the following sales:

and if there are two promotions:

sales on 10/15 will be on promo for Prod B but not A:

my suggested solution for this is to simplify the expressions.  since each sale can either be on promo or not, i would determine that status per sales record.  then in the expression:
non promo:  =sum({<IsOnPromo={'No'}>}Amount)
on promo: =sum({<IsOnPromo={'Yes'}>}Amount)
if you want total sales column: =sum(Amount)

the expressions are easier to read and maintain.  to determine if a sales record is on promo or not:

Promotion:
load * inline [
Product, StartDate, EndDate
B, 10/1/2020, 10/30/2020
A, 1/1/2020, 3/1/2020
];

Sales:
load * inline [
Product, SaleDate, Amount
A, 10/15/2020, 100
A, 2/1/2020, 100
B, 10/15/2020, 50
B, 2/1/2020, 70
];


NoConcatenate
tmpPromo:
load distinct Product, SaleDate
resident Sales;

inner join (tmpPromo)
load Product, StartDate, EndDate
resident Promotion;

NoConcatenate
IsPromo:
load distinct Product, SaleDate, if( SaleDate >=StartDate and SaleDate<=EndDate, 'Yes', 'No') as IsOnPromo
Resident tmpPromo
;

left join (Sales)
load Product, SaleDate, IsOnPromo
Resident IsPromo;

drop tables IsPromo, tmpPromo;

[StephaneP]

Hi Edwin,

Thank you for your reply and suggestion. I think it's a good approach to flag each sale with the tag promo yes/no

I have tried your script on my data and there is a problem : There is a difference of 7 million 😞

I think some sales are duplicates. I'm searching why... any ideas ?

Best regards,

Stephane

[edwin]

load your qvw if possible (or your script - just remove the confidential parts) im interested in finding out

[StephaneP]

Hi Edwin,

Sorry for the late answer, I have contracted Covid-19 last week.

I have tried to use Intervalmatch in your example (i think it was a better approach)
I have tried to add the concept of "promo type" too. 

This is my script 

SET DateFormat='DD/MM/YYYY';

Qualify *;
Unqualify Product;

Promotion:
load * inline [
Product, StartDate, EndDate, TypePromo
B, 01/10/2020, 30/10/2020, 'Black Friday'
A, 01/01/2020, 31/01/2020, 'Others'
D, 01/01/2020, 10/01/2020, 'Others'
];

Unqualify *;
Sales:
load * inline [
Product, Date, Amount
A, 15/10/2020, 120
A, 01/01/2020, 100
B, 15/10/2020, 50
B, 01/01/2020, 70
C, 01/01/2020, 200
C, 02/01/2020, 200
];

Left JOIN (Sales)

INTERVALMATCH (Date,Product)
LOAD DISTINCT 
    Promotion.StartDate, 
    Promotion.EndDate,
    Product
resident Promotion;

Left join (Sales)
LOAD DISTINCT 
    Product,
    Promotion.StartDate, 
    Promotion.EndDate,
    Promotion.TypePromo
resident Promotion;

RENAME TABLE Sales TO Sales_tmp;
QUALIFY * ;
UNQUALIFY Product;

Sales:
NOCONCATENATE
LOAD *
RESIDENT Sales_tmp;

Drop table Sales_tmp;

Drop Field Sales.Promotion.StartDate,Sales.Promotion.EndDate;
Rename Field Sales.Promotion.TypePromo to Sales.TypePromo;

The result was like expected, the sales have the good flag

I have tried to do the same approach on my datas but i have an unknown error on the loading script 😞

I can't upload my qvd. There are big and of course confidential. 

I will try to show you the loading script.

Best regards,

Stephane