I have the following issue. Our retailers sent us month to date data, every week. My need is to extract the weekly data from this MTD data. So to be more exact. We receive data from 1-5 November then the next report will include data from 1-12 November. How can I extract the data from 6-12 November, so on a weekly basis.
I'm not sure this is particularly feasible, considering weeks typically cross months. If you count weeks as starting on the 1st of each month instead (and use partial weeks), I suppose you could try something along the lines of:
1) Load the most recent file
2) If it is the first file for the month, that is the values for that month's week 1 and nothing else needs to be done
3) If it is not the first file, load the previous file as well as the current one
4) Group by all dimension fields and subtract the measure fields based on FirstSortedValue(Field, Date)
5) Store the result for the appropriate week number
This isn't really the sort of task I'd want to undertake with Qlik, or any BI tool. This seems like something you'd want to handle in a database.
