Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
m_seisbye
Contributor
Contributor

Interval match or differant approach

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. 

   

DateSalesCostMarkupLocal CostGM
Last year20101,2                   8,3           11,7
This year20101,15                   8,7           11,3
Correct total

          23,0

,

But qlikview seems to be doing it this way?

    

DateSalesCostMarkupLocal CostGM
Both years40202,35                                         8,5           31,5
Qlikview total now          31,5

Capture.JPG

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.

1 Reply
sunny_talwar

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

Preparing examples for Upload - Reduction and Data Scrambling