Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
You can probably create a additional date field DateField from the FileBaseName() or FileTime() when loading in your excel files (or maybe using Today()).
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