topic Re: Trend displayed on bar chart in QlikView

Trend displayed on bar chart

Fri, 07 Mar 2014 09:59:09 GMT

Hello,

I have a bar chart with 5 expressions detailing our total fee income over the last 5 years. The expression for the current year is:

({<BILLED_TIME.PERIOD={$(YTD)}, TIME.PERIOD_FIN_YEAR = , INT_DATE => } BILLED_TIME.BILLED_AMT)

The 'YTD' variable uses the following expression, where the currentPeriod is equal to '201412'.

">=$(=Num(Left($(currentPeriod), 4) & '01'))<=$(=Num($(currentPeriod) - 1))"

The other 4 expressions / bars in the chart are identical but for the previous 4 years. What I'd like to do, if it's possible, is to display the % increase or decrease from the previous year, somewhere on the chart. So for example if the total fee income for this year is down 5% on the previous year, I'd like to display this '-5%' figure somewhere on or above the bar for the current year. Is this possible?

Alternatively, if there is an alternative or more elegant way to do the same thing, I'd appreciate any suggestions.

Thanks, Gavin

Re: Trend displayed on bar chart

whiteline — Fri, 07 Mar 2014 11:11:36 GMT

Hi.

You can use 'Values on data points' to display the values.

Then you can use dual() function to replace the text with the expression you want but keep the numeric value that is used to plot the bar chart.

dual([the expression for % increase], [the expression for the bar])

Notice that you have to use Num() function to get the text in a proper format.

Also you have to set the 'Expression default' number format for the expression.

Re: Trend displayed on bar chart

Fri, 07 Mar 2014 11:13:31 GMT

I think you want something like below...

Please try

Re: Trend displayed on bar chart

Fri, 07 Mar 2014 11:42:06 GMT

Thanks whiteline. That would be useful, but the size of the numbers used to plot the chart - in the millions - makes these impractical to display on the size of the graph I am using. A % increase/decrease figure would be OK, but the actual figures plus % increase/decrease would make the graph quite unreadable.

Re: Trend displayed on bar chart

Fri, 07 Mar 2014 12:07:44 GMT

Thanks Vinay. That was a useful suggestion but unfortunately the required white space between the visible and invisible bars mean unacceptable changes to the overall formatting of the chart. I have decided instead to create another small pivot table below the existing chart, showing the % increase / decrease.

Gavin

Re: Trend displayed on bar chart

whiteline — Fri, 07 Mar 2014 12:14:54 GMT

Hmm.

You can calculate a bit more complex values, to show previous year values and the difference (so you should decide how to show negatives). And then use labels as I've described for that delta. Then the % values will have also a visual representation. It should make the chart readable.

Re: Trend displayed on bar chart

Fri, 07 Mar 2014 12:53:38 GMT

Thanks again - I've decided instead to insert a basic straight chart right below the main chart showing the % differences. Not quite as slick, but it looks fine and seems very readable.

Gavin