Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jlcoombs
New Contributor II

Average of Data occurring on same date each year

I have a set of data spanning a number of years.  I want to chart the average of the value for the same day for the past x years.

ie.

DayValue
1 Jan 20135
2 Jan 20136
3 Jan 20137
1 Jan 20141
2 Jan 20142
3 Jan 20143
1 Jan 20154
2 Jan 20155
3 Jan 20156

So I want to be able to plot 2015 data which is easy to extract, but then I want to be able to plot the average for that day from the past 2 years alongside. i.e. the result set becomes

Header 12015Average Last 2 Years
1 January43=(5+1)/2
2 January54=(6+2)/2
3 January65=(7+3)/2

What I'm trying to do is similar to how they report what the average temperature on this day has been over the last 10 years or so, the average is not the last 10 days, its the average for that same day of the year for the past 10 years.

Can someone assist with how to do an appropriate average or range function to get the required results.  Your assistance is much appreciated.

Jamie

Tags (1)
1 Reply
jsn
Honored Contributor

Re: Average of Data occurring on same date each year

Hi Jamie,

Please see attached example.

I create the partial date (Day & Month) in the script and then use Set Analysis in the table to pick the appropriate years to calculate based on Today's date. Since it's 2015 it will use 2015 for the first column and 2013-2014 for the average as per your example above. !

Community Browser