Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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:
@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:
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.
@Raggs How did you arrive to 7% calculation?
Hi
Create the composite key in both tables and use that composite key instead of "BrandID" in the above examples.
Hope it helps
7% has given by Business user in Excel sheet
Hello
I have created composite key, however for the customer# we are not getting the remaining filed values
@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;