Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

ignaciol
New Contributor

Rain average by month over a period of years, when there is no data in a month?

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 !!

1 Solution

Accepted Solutions
MVP
MVP

Re: Rain average by month over a period of years, when there is no data in a month?

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.

3 Replies
MVP
MVP

Re: Rain average by month over a period of years, when there is no data in a month?

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.

Not applicable

Re: Rain average by month over a period of years, when there is no data in a month?

You might want to analyze the months when there is no rain. Might be a value add.

Best Regards,
Vishal

IAMDV
Honored Contributor II

Re: Rain average by month over a period of years, when there is no data in a month?

John - Simple yet clever approach. Thank you for sharing

Cheers - DV

Community Browser