Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Monthly values for selected year and Yearly average for previous years


I need to plot the monthly cost values depending on what year the user selects and show the Yearly Avg for all the previous years. So if the user selects 2014, I need the chart to show 2012 Avg Cost (61.5) as the first bar, 2013 Avg Cost (40.75) as the second bar and then each monthly value for 2014

The data is as follows

DateCost Year
Jan-12502012
Feb-12302012
Mar-12202012
Apr-12102012
May-12502012
Jun-12902012
Jul-12802012
Aug-12502012
Sep-121452012
Oct-121252012
Nov-12362012
Dec-12522012
Jan-13522013
Feb-13892013
Mar-13262013
Apr-13152013
May-13182013
Jun-13362013
Jul-13572013
Aug-13952013
Sep-13212013
Oct-13202013
Nov-13152013
Dec-13452013
Jan-14782014
Feb-14962014
Mar-14582014
Apr-14422014
May-14532014
Jun-14622014
1 Solution

Accepted Solutions
Highlighted

Re: Monthly values for selected year and Yearly average for previous years

Is this what you are looking to do?

If yes, then PFA.

Best,

S

View solution in original post

9 Replies
Highlighted

Re: Monthly values for selected year and Yearly average for previous years

Is this what you are looking to do?

If yes, then PFA.

Best,

S

View solution in original post

Not applicable

Re: Monthly values for selected year and Yearly average for previous years

Thank you so very much. This is exactly what I needed.

Re: Monthly values for selected year and Yearly average for previous years

Great, I am glad I was able to help.

Best,

S

Not applicable

Re: Monthly values for selected year and Yearly average for previous years

I now need to add two additional lines to the bar chart,

1) Goal Line: If I select 2015, then I need a goal line for all months in 2015 (straight line) at 67*0.9 =60.3 which is a 10% reduction from the previous year average

2) Year to Date Average for all values in 2015, so for Jan it would be 35, for Feb 26, March 24.7, April 34.3 etc.Chart.png

Re: Monthly values for selected year and Yearly average for previous years

Is this what you were looking for?

PFA, if it is.

Best,

Sunny !

Not applicable

Re: Monthly values for selected year and Yearly average for previous years

Exactly, you are the best.  Thank you so much for your prompt and accurate response.

Re: Monthly values for selected year and Yearly average for previous years

Hahahaha, thanks! But as a note, I found out that the YTD Average line only works for year 2015 and 2014. before that it gives some weird results. If are going to be looking for values past 2013 (or last two years in your case) we will need to further tweak it.

Let me know what you think.

Best,

Sunny

Not applicable

Re: Monthly values for selected year and Yearly average for previous years

Yes, I was able to fix the formula to address that issue.  Thank you againl.

Re: Monthly values for selected year and Yearly average for previous years

Awesome!!! Not a problem. Always trying to learn by helping.

Best,

Sunny