1 Reply Latest reply: Nov 15, 2017 7:00 AM by Matthew Morge RSS

    Incorrect Summing of Measure

    Matthew Morge

      Hi All,

       

      I am currently working on developing a forecast accuracy report for the business that I work for. This is driven off of data within our IFS Applications ERP software. We are capturing snapshots of the forecast on a daily basis. Based on the delivery date to our customer/s I have built the logic in the script editor to return data from the correct days snapshot.

       

      However when I come to visualize this in a table in the final app it is throwing out some spurious numbers.

       

      Below is the logic from the script editor to pick the correct days snapshot based on the delivery date:

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      //The following returns the relevant Forecast QTY from the MS Set as defined per Company.

      //If the Wanted Delivery Date is greater than today then the Forecast QTY from MS Set 1 is returned.

              If(WANTED_DELIVERY_DATE > Today(),(ApplyMap('_MSSetForecastLookup',(CONTRACT&'_'&CATALOG_NO&'_'&

              WANTED_DELIVERY_DATE&'_'&'1'),'0')),(ApplyMap('_MSSetForecastLookup',CONTRACT&'_'&CATALOG_NO&'_'&

              WANTED_DELIVERY_DATE&'_'&(ApplyMap('_CompanySetLookup',COMPANY&'_'&

              (ApplyMap('_DayLookup',(DATE(WANTED_DELIVERY_DATE-ApplyMap('_CompanyOffsetMeasure',COMPANY,

              'Company Offset Value Missing'))),'MS Set Day Missing')),'MS Set Invalid')),'0'))) as FORECAST_QTY,  

       

      -----------------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      Visualization01 - Straight Table:

      The highlighted "FORECAST_QTY" field has been added as a dimension and is showing the correct qty that I would expect.

      The subsequent two fields I have added as measures and is showing incorrect figures.

       

      Expression Editor:

      Both the Dimension & Measure fields have been added with no additional calculations or formula's added but I am assuming I need to add some sort of grouping.

       

       

      I am still fairly new to Qlik Sense and have surprised myself by getting this far but could do with some advice / help to allow me to progress.

       

      Many thanks in advance.

       

      Matt

        • Re: Incorrect Summing of Measure
          Matthew Morge

          I have now identified what the issue is.

           

          Our Sales data is detailed at the following levels: Customer, Product, Delivery Date, Shipping Address whilst Forecast is Customer, Product and Delivery Date. Therefore where I am pulling in a Forecast value I am actually pulling the same value multiple times (to each record = shipping address).