Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following code. which are looking up a tax percentage based on the invoice date. This tax i need to deduct from the cost.
Rates:
Directory;
Mapping LOAD
Company,
Rate_GBP
FROM
[Basedata\Exchange Rates.xlsx]
(ooxml, embedded labels, table is Sheet1);
Sales:
LOAD
InvNumber,
InvoiceDate as CalendarDate,
InvMonth,
InvYear,
Company,
OrderNumber,
OrderType,
OrderLineNumber,
OrderDate,
SellToCustomer,
ItemNumber,
NettLocal as [Sales LCY AC],
NettLocal / ApplyMap('Rates', Company, 0) as [Sales GBP AC],
OrderCurrency,
Cost,
([Cost]/[Markup Percentage]) as [Cost without tax],
Nettlocal - cost without tax as margin
more stuff with the cost without tax.
[Local Curr]
FROM
[Sales.qvd]
(qvd) Where Len(InvNumber)>0;
Intervals:
LOAD
Collroll_From,
Collroll_To,
[Markup Percentage],
CR_Company,
CR_Country,
CR_Country &'-'& Collroll_From as [Costroll Name]
FROM
[Markup Intervals - DK TEST.xlsx]
(ooxml, embedded labels, table is Sheet1);
IntervalMatch:
IntervalMatch (CalendarDate)
Load distinct Collroll_From, Collroll_To resident Intervals;
Right now i'm doing it this way. (without the red text). this will calculate the correct amounts if i add enough dimensions. But it's only right per line in a straight table. not the totals etc.
=sum({$<CalendarYear = {$(=max(CalendarYear))}>} [Sales LCY AC])- (sum({$<CalendarYear = {$(=max(CalendarYear))}>} [cost])/ sum({$<CalendarYear = {$(=max(CalendarYear))}>} [Markup Percentage]))
It's not really working as intended. Also i'm having more problems with it. I need further fields in the load script, to simplify the formulas. So i'm thinking I need to create the Red lines in the load script. which would make it possible to sum on that one field. and not create it by dividing subtracting etc. in the charts.
But my problem are i want to load the fields in the sales table, but this is not possible since the markup aren't interval match before the sales load are done. so how would i achieve this? Also to complex it a bit. I need to add the country to the interval match. right now i'm only testing with 1 country. But in fact i have 4 countries, with different tax'es and different dates.
Should I drop the interval match and go another way? or how can i move forward.
Thank you very much
Hello Mike,
If my understanding is correct, you would want to use the field [Markup Percentage] in Sales table to calculate new fields marked in red.
You could use Extended Interval Match to bring in the Fields from Intervals table into Sales table. I have included Country level to intervals so use can match this to your script.
Intervals:
Load * Inline
[
Collroll_From,Collroll_To,Markup Percentage,Country
01/01/2017,31/01/2017,30,US
01/02/2017,28/02/2017,25,US
01/03/2017,31/03/2017,15,US
01/04/2017,30/04/2017,32,US
];
Sales:
Load * Inline
[
InvoiceNo,CalendarDate,Cost,Country
111,10/01/2017,200,US
112,02/02/2017,350,US
113,21/03/2017,400,US
114,11/04/2017,120,US
];
Left Join IntervalMatch(CalendarDate,Country)
Load Collroll_From,Collroll_To,Country Resident Intervals;
Left Join(Sales)
Load
Collroll_From,Collroll_To,[Markup Percentage],Country
Resident Intervals;
Drop Table Intervals;
Attached is the sample application for the same
Thanks for the reply.
Haven't used join before. so could you just help me understand it a bit. Let's say i want to create a field called Margin. where it takes sales / markup? where would i then put it in the script?