I can't seem to find any discussion on this, hence this new post.
I have raw data like this:
Day 5 = 30
Day 4 = 40
Day 3 = 25
Day 2 = 20
Day 1 = 45
I need a a report that shows the sum of the Past-N-Days window; e.g. N=2 and 3:
<Day>|<Past 2 days>|<Past 3 days>
Day 5 | 70 | 95
Day 4 | 65 | 85
Day 3 | 45 | 90
Btw, I have been attempting to solve the problem using SET analysis. The obstacle seems to be that it is impossible to aggregate data beyond the current dimension value. For example, I can't really get the data on Day 4 when my expression is dimensioned by Day 5. Or perhaps my approach in wrong.
Here's one approach. Two fields, AsOfDay and DaysBack, are explicitly tied to every single day that should be included in that cell. Then you just need a pivot table with those dimensions, and sum(Value) as the expression. Simple!
Except... it won't scale up very well. Basically, you're getting in the neighborhood of the CUBE of the number of days in rows. I already have 2870 rows just for 20 days. If I bump that to 200 days, I get 2,686,700 rows. So it very quickly becomes completely impractical.
This looks the same on the surface, except that now our AsOfDay and DaysBack fields are islands. They aren't connected to the main data. Instead, we use an IF statement in the chart to connect them. This CAN become impractical, in that if you have 10,000 days you're looking at, and decide to look at all of them at once, and to up to 10,000 days back, I'm sure that's far more data than the chart could ever process. But even if that much data is available, it's ridiculous to look at that much at once. So in practice, for the number of days and days back that people will likely want to look at, I suspect it would work fine.
Thinking further, if you're willing to explicitly limit the number of days back, you can get around the limitation of having too many rows in my first solution. And you can get around even squaring the number of days by using a nice while loop. That might make this a pretty good solution in practice, since it has the advantage of chart simplicity while still scaling up just fine to any number of days.
Thanks for the hint. It turns out range sum does not include the current row's "today value" (I believe it is beacause we need to turn above() to "below" using -1), so I have to change it to the following and seems to work correctly now. If you can confirm my change is correct that would be great. Thanks:
Original:
Past 2 Day = rangesum(above(column(1),-1,2))
Past 3 Day = rangesum(above(column(1),-1,3))
New:
// reduce above()'s window by one, and add back the current row value instead.
Past 2 Day = column(1)+rangesum(above(column(1),-1,1))
Past 3 Day = column(1)+rangesum(above(column(1),-1,2))
If your formula works go with it. I don't see anything technically wrong with it.
However, I think you'll get the same result if you use the following formula that might be easier to look at:
rangesum(below(column(1),0,2))
The current row should be included with the value 0 in the second paramente of the functions above() or below(). I think the error was that my days were backwards. I was thinking Day 1 was the most recent day instead of Day 5. And for some odd reason the numbers matched.
Karl, thanks. I just notice the "below" function as I scroll down on the reference manual page.
It turns out there is one more requirement that would extend this thread. It is actually very different from the first requirement but I hope somehow I can leverage above() or similar functions.
What we are counting is actually a usage, e.g. people that ride the bus every day. therefore, what we also like to count in addition to the above, is the UNIQUE bus rider over the day range. I have been trying to use the above() inside a SET expression but no luck so far.
I had to think about this for a little bit. There doesn't appear to be a way to use inter-record functions to do a count distinct between various rows. You can only sum the count distinct of each separate day.
Also, with set analysis you can't use the value of the dimension in one line of the table as a reference to sum or count between other lines on the table.
So, the only option I can think of is to use the classic sum(if) with an island table. The only problem with this solution is the time it might take to calculate the table in QlikView.
I've attached an example using sum. You would replace the function with count(distinct rider_id)
