topic Re: add calculated field to interval match in QlikView

add calculated field to interval match

Anonymous — Tue, 11 Apr 2017 07:55:32 GMT

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

Re: add calculated field to interval match

malini_qlikview — Tue, 11 Apr 2017 14:56:12 GMT

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

Re: add calculated field to interval match

Anonymous — Tue, 18 Apr 2017 11:02:17 GMT

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?