1 Reply Latest reply: Jun 27, 2017 10:38 AM by Sunny Talwar RSS

    Interval match or differant approach

    Mike Seisbye

      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.