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
