Logic to get day level data from MTD

[nareshthavidishetty]

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..

[swuehl]

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-fun...

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