I am trying to do something tricky and could use some help. I have a number of dates for which I also have a number of transactions. Assume that, as a first step, we do a sum of these transactions per day using a group by but still the group by wont only be on date so we might have a various number of rows per each date.
What I want to do is calculate for each row the average of these sums for that date and the 6 previous ones and save it as a new field. It is worth noting that I dont have all calendar dates available and as I said could be more than one rows per date even after group by so i am not sure how helpful functions like previous () etc can be unless the date can be checked specifically..
Ok, so, there were 2 mistakes, first my intervalmatch should not use existing field names, so I have renamed in tmp2 the two dates. Then, it's the average of the daily transations and not the average of each transaction, so tmp2 is not more based on transaction_table but on tmp1.
I have also renamed the amount fields to have something easier to understand.
And yes the first code was written without Qlikview, else I will not have wrote "interval match" instead of "intervalmatch" .
If you have a SQL database in source, like Oracle or SQLServer you can use a partition by windowing function.
Else, if you can't, maybe you can create a second transaction date in your table which is 7 days after the real one. Then, with a interval match joining the table with itself you will be able to group all the transactions.
I understand you already have some chart displaying the sum of whatever per day?
There are functions in QlikView - BEFORE and AFTER I think, haven't yet found a use for them, but they ARE useful - which allow you to access the different "points" along your dimension which is the day.
I think that would be a good starting_point for a rolling average in a chart.
Sorry I cannot help you further, as I said I haven't yet done it.