Inspired by the post "The importance of nothing": https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/28/the-importance-of-nothing?et=blogs.co...
and perhaps this Calculating rolling n-period totals, averages or other aggregations
I would like to discuss this case:
Lets use this simple data set:
ET DateFormat='DD-MM-YYYY';
$(Must_Include=Calendar.qvs);
SomethingOrNothing:
LOAD * INLINE [
DateK, Number, Categori
01-01-2015, 17, A
01-02-2015, -, A
01-03-2015, 14, A
01-05-2015, 16, A
01-06-2015, ?, A
01-07-2015, 11, A
01-09-2015, 10, B
01-10-2015, 17, A
01-11-2015, , A
01-12-2015, 15, A
];
Call Calendar('DateK','MasterCalendar');
What if we would like to do 6 month average for Category A? I would like to select either April, September or December in the master calendar and show an KPI from the selected month and average 6 month back. What is the correct 6 month average / Month rolling for each of these months?
What is the correct average and how should it be calculated?
This is the data there is, but for the last 15 years this "Number" have been between 10 and 20, though hopefully it is a ratio scale.