Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Highlighted
Fabien
New Contributor III

Add up/down arrows to bar chart

Hi everyone,

 

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

Screenshot - 9_12_2019 , 12_10_15 PM.png

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Add up/down arrows to bar chart

Maybe try this:

Remove the arrow dimension and try with number formatting:

MC.PNG

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

View solution in original post

9 Replies
Highlighted
Luminary
Luminary

Re: Add up/down arrows to bar chart

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
New Contributor III

Re: Add up/down arrows to bar chart

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

Screenshot - 9_12_2019 , 4_15_22 PM.png

Highlighted
Partner
Partner

Re: Add up/down arrows to bar chart

I am able to get this:

 

MC.PNG

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.

 

 

Highlighted
Luminary
Luminary

Re: Add up/down arrows to bar chart

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.

Highlighted
Partner
Partner

Re: Add up/down arrows to bar chart

Shifting your dimension to the right might looks better:

MC.PNG

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

Highlighted
Fabien
New Contributor III

Re: Add up/down arrows to bar chart

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

Highlighted
Partner
Partner

Re: Add up/down arrows to bar chart

Maybe try this:

Remove the arrow dimension and try with number formatting:

MC.PNG

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

View solution in original post

Highlighted
Fabien
New Contributor III

Re: Add up/down arrows to bar chart

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

Highlighted
Fabien
New Contributor III

Re: Add up/down arrows to bar chart

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

Screenshot - 18_12_2019 , 11_43_25 AM.png