Interval match or differant approach

Anonymous — Tue, 27 Jun 2017 12:31:06 GMT

So if have tried using this interval match.

Rates:

Directory;

Mapping LOAD

Company,

Rate_GBP

FROM

[Basedata\Exchange Rates.xlsx]

(ooxml, embedded labels, table is Sheet1);

Sales:

LOAD

InvNumber,

InvoiceDate as CalendarDate,

12*(Year(Today())-Year(InvoiceDate)) +Month(Today())-Month(InvoiceDate) as MonthsAgo,

InvMonth,

InvYear,

Company,

OrderNumber,

OrderType,

Qty,

ItemNumber,

NettLocal as [Sales LCY AC],

NettLocal / ApplyMap('Rates', Company, 0) as [Sales GBP AC],

BPTotalCost,

OriginalQty,

OriginalNettLocal,

[Cost] as [Group Cost],

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;

What i get is a percentage markup for the invoice date. since the percentage are changing. Then the interval match can tell me the correct markup percentage for that invoice date. This is working if i'm showing all the lines for each invoice date.

But if i need to sum across several different markups. It goes sideways 😞

So i would love to be able to put in the match value as a static value to the table.

What i need in the end are to take the cost and remove the markup. So let's say the cost is 10. and i know for that invoicedate the markup was 20%. Then i would like to do it in the residential load

Load ...*

[Cost] as [Group Cost],

[Cost] / [markup percentage] as [Local Cost]

From Sales.qvd

But i can't find a way to do this.

My problem are today if i do it like above and i said =SUM( Sales - ( [Cost] / Markup percentage]) ) I don't get the Gross profit. i get some kind of average which aren't correct. i have tried illustrating below.

Date	Sales	Cost	Markup	Local Cost	GM
Last year	20	10	1,2	8,3	11,7
This year	20	10	1,15	8,7	11,3
Correct total	23,0

But qlikview seems to be doing it this way?

Date	Sales	Cost	Markup	Local Cost	GM
Both years	40	20	2,35	8,5	31,5
Qlikview total now	31,5

I have created a sample of the application. but i can't remove the all the sensitive parts. so i can send it seperatly if it's needed.

Re: Interval match or differant approach

sunny_talwar — Tue, 27 Jun 2017 14:38:06 GMT

A sample might really help see the issue. For confidentiality concerns, look at this thread

Preparing examples for Upload - Reduction and Data Scrambling

topic Re: Interval match or differant approach in QlikView

Interval match or differant approach

Re: Interval match or differant approach