Calculating rolling n-period totals with missing periods

Anonymous — Wed, 01 Mar 2017 02:24:48 GMT

Hi folks,

I've been researching how to implement in Qlik Sense what seems like a simple question on the surface but to date I have had no luck. I'm either overthinking it or simply way out of my depth on this one.

The problem is quite simple.

Given a sales event I wish to sum the sales occurring from that date within the next n periods. Could be 7 days, 1 month etc.

There are many dates, in this instance they relate to store visits, many customers and many sales.

I've had no luck using variations of the expressions similar to the below which are based on the post (Set Analysis for Rolling Periods).

Sum({<Bill_Date={‘>=$(=Date(Bill_Date)+1))<=$(=Date(Bill_Date)+7))’}>} Sales )

Additionally looking at excellent posts related to calculating rolling periods (https://community.qlik.com/docs/DOC-4) and Accumulative Sums all appear to assume that that period is present based on the next n entries in the data.

In the data we are working with there may not be any sales within the date frame however there would be subsequent sales. Counting the next values using RangeSum() thus appear to not work or would likely give results calculated from outside the date range.

So at this point I am looking for a little guidance. Maybe I have already past the correct solution but haven't realized it or maybe there is a different area I should be looking. As always any direction would be appreciated.

Thanks

Re: Calculating rolling n-period totals with missing periods

sunny_talwar — Wed, 01 Mar 2017 09:47:22 GMT

I think a better solution might be to use The As-Of Table

topic Re: Calculating rolling n-period totals with missing periods in App Development

Calculating rolling n-period totals with missing periods

Re: Calculating rolling n-period totals with missing periods