Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
