topic Re: Percent change in bar chart in QlikView

Percent change in bar chart

mhassinger — Thu, 05 Jun 2014 18:58:24 GMT

I have a bar chart which shows sales by year. Each bar is a simple Sum(Sales) expression.

As a value on the data point, I want to not only display the dollar amount, but also the percentage change from the last year.

So if my sales were: 2012 = 50, 2013 = 75, 2014 = 60, I'd want to see the following three values over each bar:

$50 / N/A $75 / +50% $60 / -20%

I have created a 2nd expression and disabled bar, kept show value on data point, but I'm not quite getting the right set analysis and formula to get the correct percentage to show.

Any ideas?

Re: Percent change in bar chart

venkatg6759 — Thu, 05 Jun 2014 19:03:45 GMT

Try similar to the below expression to get both the % and $ amount in the chart

dual(Sum({<Date={'2012','2013','2014'}>}Sales)&' ('& num(Sum({<Date={'2012','2013','2014'}>}Sales)/Sum(sales),('##%'))& ')',

Sum({<Date={'2012','2013','2014'}>}Sales)+(Sum({<Date={'2012','2013','2014'}>}Sales))/100)

Re: Percent change in bar chart

rustyfishbones — Thu, 05 Jun 2014 19:13:51 GMT

Do you mean like this

Re: Percent change in bar chart

rustyfishbones — Thu, 05 Jun 2014 19:19:18 GMT

sorry I mean this

Re: Percent change in bar chart

mhassinger — Thu, 05 Jun 2014 19:52:20 GMT

Close! I need the 50% and -20% to be next to the 75 and 60. But the formula you're using in that expression would probably be helpful!

Re: Percent change in bar chart

Thu, 05 Jun 2014 19:55:58 GMT

You could use a combo chart. Make the bars your normal expression, then add an expression such as:

dual('$(=(1-above(sum(Sales))/100)),sum(sales))

Make this new expression a dot, and select show values on data points.

Re: Percent change in bar chart

rustyfishbones — Thu, 05 Jun 2014 20:07:20 GMT

I have added that above, mark if helpful or correct

Re: Percent change in bar chart

mhassinger — Fri, 06 Jun 2014 18:15:51 GMT

Thanks, all! It was the Above function that really de-mystified what I was trying to do!

Re: Percent change in bar chart

Fri, 06 Jun 2014 18:19:11 GMT

No problem! Good luck