2 Replies Latest reply: Apr 18, 2017 7:02 AM by Mike Seisbye

# add calculated field to interval match

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;

Company,

Rate_GBP

FROM

[Basedata\Exchange Rates.xlsx]

(ooxml, embedded labels, table is Sheet1);

Sales:

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:

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

• ###### Re: add calculated field to interval match

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:

[

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:

[

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)

Left Join(Sales)

Collroll_From,Collroll_To,[Markup Percentage],Country

Resident Intervals;

Drop Table Intervals;

Attached is the sample application for the same