Logic to get day level data from MTD

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have an excel source data with out date field as below

Data on:5/17/2016

Warehouse	TotalCost
132	102701.3
150	178464.6
144	123523.4

Data on 5/16/2016

Warehouse	TotalCost
132	202701.3
150	478464.6
144	723523.4

Here the MTD value is sum(TotalCost) will get MTD sales

To get daily sales as mathematically we need to do today MTD sales - Yesterday MTD sales will give Today sales.

We need to do this in qlikview at script level,but my excel source don't have any date field.

Thanks..

Re: Logic to get day level data from MTD

swuehl — Tue, 17 May 2016 13:38:56 GMT

You can probably create a additional date field DateField from the FileBaseName() or FileTime() when loading in your excel files (or maybe using Today()).

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/FileFunctions/file-functions-script.htm?q=…

The concatenate all your data into one table and do a resident LOAD like

LOAD Warehouse, TotalCost, DateField,

Rangesum(TotalCost, -If(Peek('Warehouse')=Warehouse, Peek('TotalCost'))) as DailyCost

RESIDENT YourTable

ORDER BY Warehouse, DateField asc;

DROP TABLE YourTable;

edit: Dropped the original table

topic Re: Logic to get day level data from MTD in QlikView

Logic to get day level data from MTD

Re: Logic to get day level data from MTD