I was wondering if anyone could help me get around what I think is a limitation of the accumulate functionality in QlikView charts.
My goal is to show a 3 month rolling 7 day sales chart.
The 3 month period is always 3 months of the sales history from the current date. I need to accumulate for 7 days to smooth out sales.
Currently I calculate the data for this in my backend database which I display as a flat chart that you can not interactive with (make selections etc).
I know that you can do this in QlikView directly by set analysis for the restriction of dates and then selecting accumulate 7 steps back for my expression. However the problem with this is that for the first 6 data points on my chart I see a slope going up due to there being less than 7 data points for 1 - 6.
Is there away of getting round this without showing the extra 7 days before the 3 months starts? Or is this just a known limitation of QlikView?
Here is a screen shot of the chart showing correctly done via my database backend technique
This is just how QlikView works - if you want a rolling 7 day total or average, your first 6 days will not have a full accumulation, because you told it to EXCLUDE those days by making selections. But there are also ways around it, depending on what you really wanted to display.
For instance, let's say you want the rolling average price of something for 50 days. You might normally write this as avg(Price)/50 and accumulate 50 steps back. But the first 50 days won't actually have the right number. One way to solve the problem is to use rangeavg(above(Price,0,50)). You might have a lot more price variability in the first few days of the date range, but that's legitimate.
I'm sure there's also a way to use set analysis to grab 50 days prior no matter what date range is selected, but then you still have the problem when your set is the whole date range.
Not sure which approach you want. Attached is the example I described.