Date Range Mapping(From Date and To Date Mapping with Invoice Date from Sales Table )

[Raggs]

Hello Team,

I have a Sales Table and Need to calculate some expenses(Like Commission and Rebate) on Sales.

Rules:  Need to calculate Expenses based on Date Ranges(Means: Need to calculate percentage on sales In between the From Date and To Date only )

Below given Sample Data.

Matrix Rules table
BrandID	From Date	To Date	Percentage	Sales
100	01-01-2016	30-06-2016	2.1	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
150	01-09-2016	31-12-2016	5.5	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
125	01-01-2016	30-06-2016	6.3	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
321	01-01-2016	31-12-2016	4.75	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
546	01-01-2016	31-12-2016	2.5	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)
256	01-09-2016	31-12-2016	1.25	Need to get the Sales from Sales table (in between FromDate and ToDate Sales only)

From Sales Table
BrandID	Product ID	Invoice Date	Sales
100	a125	04-01-2016	6500
150	s365	25-01-2016	4500
125	x456	12-02-2016	1250
321	d145	06-03-2016	3650
546	z124	25-07-2016	7850
256	v111	07-07-2016	1256
125	x456	11-08-2016	356
321	d145	15-09-2016	1254
546	z124	11-12-2016	650
256	v111	28-11-2016	950
125	x456	04-05-2016	75

Please check and help me on this ASAP.

Thanks in advance.

[MayilVahanan · Accepted solution]

Hi @Raggs 

You can achieve based on IntervalMatch concept.

Try like below

T1:
LOAD * INLINE [
BrandID, FromDate, ToDate, Percentage
100, 01-01-2016, 30-06-2016, 2.1
150, 01-09-2016, 31-12-2016, 5.5
125, 01-01-2016, 30-06-2016, 6.3
321, 01-01-2016, 31-12-2016, 4.75
546, 01-01-2016, 31-12-2016, 2.5
256, 01-09-2016, 31-12-2016, 1.25
];

T2:
LOAD * INLINE [
BrandID, ProductID, InvoiceDate, Sales
100, a125, 04-01-2016, 6500
150, s365, 25-01-2016, 4500
125, x456, 12-02-2016, 1250
321, d145, 06-03-2016, 3650
546, z124, 25-07-2016, 7850
256, v111, 07-07-2016, 1256
125, x456, 11-08-2016, 356
321, d145, 15-09-2016, 1254
546, z124, 11-12-2016, 650
256, v111, 28-11-2016, 950
125, x456, 04-05-2016, 75
];

IntervalMatch([InvoiceDate],BrandID)
LOAD
[FromDate], [ToDate],BrandID
Resident T1;

Join
LOAD * Resident T2;

Join
LOAD * Resident T1;

DROP Table T1, T2;

Output:

[Taoufiq_Zarra]

@Raggs  May be

in Script :

Data:

LOAD * INLINE [
    BrandID, From Date, To Date, Percentage
    100, 01-01-2016, 30-06-2016, 2.1
    150, 01-09-2016, 31-12-2016, 5.5
    125, 01-01-2016, 30-06-2016, 6.3
    321, 01-01-2016, 31-12-2016, 4.75
    546, 01-01-2016, 31-12-2016, 2.5
    256, 01-09-2016, 31-12-2016, 1.25
];

join

LOAD * INLINE [
    BrandID, Product ID, Invoice Date, Sales
    100, a125, 04-01-2016, 6500
    150, s365, 25-01-2016, 4500
    125, x456, 12-02-2016, 1250
    321, d145, 06-03-2016, 3650
    546, z124, 25-07-2016, 7850
    256, v111, 07-07-2016, 1256
    125, x456, 11-08-2016, 356
    321, d145, 15-09-2016, 1254
    546, z124, 11-12-2016, 650
    256, v111, 28-11-2016, 950
    125, x456, 04-05-2016, 75
];

Final:
noconcatenate

load *,if([Invoice Date]>=[From Date] and [Invoice Date]<=[To Date],1,0) as Flag resident Data;

drop table Data;

and in chart

dimension : BrandID, From Date, To Date, Percentage

Measure :  =sum({<Flag={1}>}Sales)

output:

[Raggs]

Hello @MayilVahanan 

Thanks for your suggestion.

Below is the Sales data from Sales table:

BP Category 1 ID	Business Partner ID	CustGroup	Brand ID	Line ID	Item Family ID	Item Number
653000	653000-001	101	001	145	8	66020-000

Below is the Excel sheet Rules to calculate the Expense(Rebate):

Customer#	Customer Cat	Cust Group	Brand ID	Line ID	Family ID	Item #	From date	To date	Percentage
653000-001							01-01-2016	31-3-2016	7.00

Need to Get the Filed(Customer Cat, Cust Group, Brand ID, Line ID, Family ID, Item# ) data information from the Sales table and Then, Need to calculate the Rebate of 7% on Customer# Sales between the Date range From Date and To Date

Is it possible with Interval Match, If Yes Can you please Help me with Interval match function.

Thanks in Advance.

[Kushal_Chawda]

@Raggs  How did you arrive to 7% calculation?

[MayilVahanan]

Hi 

Create the composite key in both tables and use that composite key instead of "BrandID" in the above examples.

Hope it helps

[Raggs]

@Kushal_Chawda 

7% has given by Business user in Excel sheet

[Raggs]

Hello 

I have created composite  key, however for the customer# we are not getting the remaining filed values 

[Kushal_Chawda]

@Raggs   try below.

Make sure that the sequence of fields in composite key  is same at both the place.

Map_Percent:
mapping load Customer#&'|'&[Customer Cat]&'|'&[Cust Group]&'|'&[Brand ID]&'|'&[Line ID]&'|'&[Family ID]&'|'&[Item #]&'|'&floor(Date) as Key,
         Percentag;    
load 
Customer#,
[Customer Cat],
[Cust Group],	
[Brand ID],	
[Line ID],
[Family ID],
[Item #],
[From date]+ iterno()-1 as Date	,
[To date],	
Percentag
FROM Excel
while [From date]+ iterno()-1<=[To date];

Sales:
LOAD [BP Category 1 ID],
     applymap('Map_Percent',
     [Business Partner ID]&'|'&[BP Category 1 ID]&'|'&[Business Partner ID]&'|'&CustGroup&'|'&[Brand ID]&'|'&[Line ID]&'|'&[Item Family ID]&'|'&[Item Number]&'|'&floor([Invoice Date])) as Percent,
    [Product ID]
    CustGroup,	
    [Brand ID],
    [Line ID],	
    [Item Family ID],	
    [Item Number],
    [Product ID], 
    [Invoice Date]
FROM Sales;