Hi All,
I am trying to create a secondary table from my primary table to include aggregated values per dimension. For the example given below where I have a daily record of 'Item Total' I would like to create a table which sums it up by month. I would also like to add a column which gives the max and min date for each store. To put it in context, the difference between the max and min date gives me how long the store has been open for. Please note that I have only added records for only 5 days.
Is it possible to do this on the load script in QlikView? If so, what are the functions/load syntax that I can use to aggregate by store id and month and do the monthly sum?
| Store ID | Item Total | COB Date | Snapshot |
| X | 10 | 01/01/2018 | Jan |
| X | 10 | 02/01/2018 | Jan |
| X | 10 | 03/01/2018 | Jan |
| X | 10 | 04/01/2018 | Jan |
| X | 10 | 05/01/2018 | Jan |
| X | 5 | 01/02/2018 | Feb |
| X | 5 | 02/02/2018 | Feb |
| X | 5 | 03/02/2018 | Feb |
| X | 5 | 04/02/2018 | Feb |
| X | 5 | 05/02/2018 | Feb |
| Y | 7 | 01/01/2018 | Jan |
| Y | 7 | 02/01/2018 | Jan |
| Y | 7 | 03/01/2018 | Jan |
| Y | 7 | 04/01/2018 | Jan |
| Y | 7 | 05/01/2018 | Jan |
| Y | 6 | 01/02/2018 | Feb |
| Y | 6 | 02/02/2018 | Feb |
| Y | 6 | 03/02/2018 | Feb |
| Y | 6 | 04/02/2018 | Feb |
Aggregated table I want to achieve:
| Store ID | Item Total | Snapshot | Min Date | Max Date |
| X | 50 | Jan | 01/01/2018 | 05/02/2018 |
| X | 25 | Feb | 01/01/2018 | 05/02/2018 |
| Y | 35 | Jan | 01/01/2018 | 05/02/2018 |
| Y | 24 | Feb | 01/01/2018 | 05/02/2018 |
Many thanks,
Raj
