Add up/down arrows to bar chart

[Fabien]

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

[Rodj]

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.

[Fabien]

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 %?

[Arthur_Fong]

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.

[Rodj]

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.

[Arthur_Fong]

Shifting your dimension to the right might looks better:

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

[Fabien]

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

[Arthur_Fong · Accepted solution]

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

[Fabien]

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

[Fabien]

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: 😎