Discussion Board for collaboration related to Creating Analytics for QlikView.
I need to make a chart with the rain average by month, for a period of years. The only Dimension is Month and the average should be for the period of years including those that didn't rain.
I can not use the avg function, because on several years no rain has fallen. Which functions should I use ??
Thank you !!
Go to Solution.
So you have data like this?
Year, Month, Rain2008, January, 52009, January, 102011, January, 5
And you want to show that the average for these four years is 5, because the missing year 2010 should count as 0? I think this:
sum(Rain)/(1+max(total Year)-min(total Year))
Edit: Alternatively, fill in the missing data in your data model. This would allow for the much simpler avg(Rain), and should calculate faster.
You might want to analyze the months when there is no rain. Might be a value add.
John - Simple yet clever approach. Thank you for sharing
Cheers - DV