Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!
So you have data like this?
Year, Month, Rain
2008, January, 5
2009, January, 10
2011, 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.
So you have data like this?
Year, Month, Rain
2008, January, 5
2009, January, 10
2011, 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.
Best Regards,
Vishal
John - Simple yet clever approach. Thank you for sharing
Cheers - DV