topic Re: Add up/down arrows to bar chart in App Development

Add up/down arrows to bar chart

Fabien — Wed, 22 Dec 2021 15:54:26 GMT

Hi everyone,

I have pretty simple bar chart, to which i would like to add trend arrows up/down versus the previous week.

I have a few 'Alternative dimensions' within this chart.

I have found a tutorial however i didn't manage to replicate the same logic (mainly because i am using QlikSense not QlikView).

Below is my current Measure:

=if((GetFieldSelections([Quote Type])='DQ' OR GetSelectedCount([Quote Type])=0),

If(GetSelectedCount([Week Num])=0,
Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)/Sum({$<[Week Num]={"$(=week(today())-1)"}>} TEU)
,
Sum({$<[Quote Type]={"DQ"}>} TEU)/sum(TEU)),

'N/A')

Appreciate any help.

Fabien

Re: Add up/down arrows to bar chart

Rodj — Mon, 09 Dec 2019 03:45:22 GMT

Hi Fabien,

As far as I'm aware we don't have that level of control over the formatting of the combo or bar charts in Qlik Sense at this stage unfortunately. There might be an extension that allows you to achieve what you are after, or something similar. In a bar chart you could conceivably colour the bars according to whether the measure goes up or down but it's not a logic I've tried. In a combo chart you might be able to use separate measures for "up" and "down" to give you the appropriate symbols, but again it isn't something I've tried.

Re: Add up/down arrows to bar chart

Fabien — Mon, 09 Dec 2019 05:27:36 GMT

Thanks for your response Rodj.

I managed to add the arrows by adding a second 'Measure' with the following expression, which leads me to think that somehow it might be possible...

=if((Sum({$<[Week Num]={'$(=Max([Week Num])-1)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-1)'}>} TEU))
-(Sum({$<[Week Num]={'$(=Max([Week Num])-2)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-2)'}>} TEU))
>0
,'▲','▼')

Giving me the following result which seems to be correct, however how do i now move these icons next to the first measure %?

Re: Add up/down arrows to bar chart

Arthur_Fong — Mon, 09 Dec 2019 06:04:58 GMT

I am able to get this:

Instead of adding as expression, add the arrows as dimension:

Arrow Dimension:

if(aggr((Sum({$<[Week Num]={'$(=Max([Week Num])-1)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-1)'}>} TEU))
-(Sum({$<[Week Num]={'$(=Max([Week Num])-2)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-2)'}>} TEU)),<Dim1>)
>0
,'▲','▼')

At Appearance>Colors and legends>Show legend>Off.

Re: Add up/down arrows to bar chart

Rodj — Mon, 09 Dec 2019 06:10:48 GMT

Yeah it's getting the symbols at the end that is the trick. Having a value calculated in a combo chart will put it in the right place but I don't know if there's a way to control the symbol in that instance. Seems like you are close and there should be a way to do it. Putting the symbol in the dimension is a cool idea.

Re: Add up/down arrows to bar chart

Arthur_Fong — Mon, 09 Dec 2019 06:24:24 GMT

Shifting your dimension to the right might looks better:

At Appearance > Colors and legend >Y-axis > Position >Right.

Re: Add up/down arrows to bar chart

Fabien — Tue, 10 Dec 2019 05:57:49 GMT

Thank you all for your responses, it's getting there but unfortunately it's not looking the way i wished it did...

Re: Add up/down arrows to bar chart

Arthur_Fong — Tue, 10 Dec 2019 06:20:34 GMT

Maybe try this:

Remove the arrow dimension and try with number formatting:

At measurement:

if((Sum({$<[Week Num]={'$(=Max([Week Num])-1)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-1)'}>} TEU))
-(Sum({$<[Week Num]={'$(=Max([Week Num])-2)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-2)'}>} TEU))
>0,

num((Sum({$<[Week Num]={'$(=Max([Week Num])-1)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-1)'}>} TEU))
-(Sum({$<[Week Num]={'$(=Max([Week Num])-2)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-2)'}>} TEU))
,'##% ▲'),
num((Sum({$<[Week Num]={'$(=Max([Week Num])-1)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-1)'}>} TEU))
-(Sum({$<[Week Num]={'$(=Max([Week Num])-2)'},[Quote Type]={'DQ'}>} TEU)/Sum({$<[Week Num]={'$(=Max([Week Num])-2)'}>} TEU)),'##% ▼'))

At Number Formatting > Select Measure Expression

Thanks and regards,

Arthur Fong

Re: Add up/down arrows to bar chart

Fabien — Wed, 11 Dec 2019 03:34:45 GMT

Thanks for that Arthur, thanks everyone, I think it's the closest we're gonna get. 🙂

Re: Add up/down arrows to bar chart

Fabien — Wed, 18 Dec 2019 00:49:43 GMT

I had to further play around with Arthur's code, but I'm finally happy with the outcome.

Here is the final code and look for those that might help:

if((Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU))/(Sum({$<[Week Num]={"$(=week(today())-1)"}>} TEU)*100)
-((Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-2)"}>} TEU))/(Sum({$<[Week Num]={"$(=week(today())-2)"}>} TEU)*100))

>0,

(Num(round(Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)/Sum({$<[Week Num]={"$(=week(today())-1)"}>} TEU)*100),'###% ▲')),
(Num(Round(Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)/Sum({$<[Week Num]={"$(=week(today())-1)"}>} TEU)*100),'###% ▼'))),

This is how it looks: 😎