Static copy of Qlikview data to compare to live

[stig1984]

I have a request from one of my users to create a 'static' version of the profitability report which can be compared to the live version.

The intended use is to create a static copy of the report data on a weekly basis - this can then be compared daily to the live version to check for changes that then need to be considered for this weeks reporting (changes will be input as an adjustment in both weeks to bring the live data in line with the reported values) -

     for example in week 1 we sold 2,000 cases for £1.00 with a cost of £0.80 cases and this was reported to H/O as £400 profit.  In week 3 the cost price of that consignment to £0.85, changing the profit in week 1 to £300.  This will show in the live data in Qlikview but this then differs from the reported result.  The adjustment to import would be -£100 in week 1 and +£100 in week 3.

The user currently does this with exported versions of the report but would like a 'live' comparison report.

The only way I can think of doing this is to export the data as as snapshot once complete and then reload this as new data fields.  The apps we use are already data heavy so this isn't a perfect solution.

Does anyone else have any better ideas before I start down this road?

[Peter_Cammaert]

There are a number of possible routes you can follow to offer benchmarking/historical data:

• Set-up a schedule to create a copy of the document that is published in the AP but doesn't get reloaded. A bit like in the Newsstand of NPrinting.
• Start versioning your base data, e.g. at the end of a reload script run tag all data with a date or a version string and save the tables to tagged QVDs. Then - as a final step - load whatever tagged QVD's are available. You won't need every single day in your history set, but maybe just the ones generated on mondays or something. This technique allows you to do with the historical data set whatever you want or can do with the current data set. Ultimate flexibility but a lot of rows in a single document
  (You could also Binary Load the previous data model of your document before adding the current data set. Might make it a bit more complex internally.)
• Only save the aggregated data (KPI's, sales per product per customer instead of all individual transactions, etc.) in tagged QVDs and load them during the next load script run. Can save you quite some RAM and disk space, depending on the number of new rows that are generated between reloads.

Best,

Peter

[marcus_sommer]

If I understand your scenario right than there is a 1:1 relation from the article in the sales fact-table to the article-price within the article dimension-table and therefore each change of the price will change all related calculations from all times.

I think I would just create an extra-field within the fact-table which contained the price at the moment when these records are read - and of course these information must be stored to be consistent. Maybe once as qvd together with the entire fact-table and once just the key-fields (probably order-id + article-id) and the price within a csv (just to ensure that if something with incremental loadings went wrong that these data are further available). Further I could imagine that if these data aren't really available within your ERP / database that they are created/stored there.

- Marcus

[stig1984]

Thanks - it's not a 1:1 relationship but I can see that working if it was.  The article that arrives can be spread across multiple sales and a sales can consist of multiple articles.  However the cost of the article can change between receipt and sales and can further change later (exchange rate fluctuations, transport costs estimate vs. actual &c. plus some consignments are purchased on a commission basis which is zero value until they are all sold)

Nice idea though in different circumstances.

[stig1984]

Thanks - option 3 above is the route I was going along which is looking like the best option.