Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chitemerere
Valued Contributor

Variance arrows in QlikSense

I have a table in Qlik Sense and would want to show up or down arrows when the 'Year to Date Value' is less than the 'Last Year to Date Value' and i have tried using the following set analysis for the column 'Variance':

If(Sum({$<$(vSetYTD)>}(Amount)) < Sum({$<$(vSetLYYTD)>}(Amount)),chr(9660,chr(9650)))


this gives a blank in the table as follows:


Screen Shot 2016-01-03 at 00.07.58.png

What could be wrong with this?


Regards.

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Variance arrows in QlikSense

Hi Christopher,

for starters, one of the parentheses in your formula is misplaced. The corrected formula should look like this:

If(Sum({$<$(vSetYTD)>}(Amount)) < Sum({$<$(vSetLYYTD)>}(Amount)),chr(9660),chr(9650))


Then, you can show your variance in a lot more elegant way if you simply attach the CHR() symbols to your positive and negative parts of the number format. For example, the following formula shows Year over Year Change %, with the appropriate arrow up or down. The first part of the numeric formatting is the "positive" format and the second part is the "negative" format.


NUM(

     Sum({$<$(vSetYTD)>} Amount) / Sum({$<$(vSetLYYTD)>} Amount) - 1,

     chr(9660) & '#,##0.0%;' & chr(9650) & '#,##0.0%'

)


You can learn this trick, along with many other advanced techniques, at the Masters Summit for Qlik. Or, you can also learn many of them from my new book QlikView Your Business.


Cheers,

Oleg Troyansky

2 Replies
MVP
MVP

Re: Variance arrows in QlikSense

Hi Christopher,

for starters, one of the parentheses in your formula is misplaced. The corrected formula should look like this:

If(Sum({$<$(vSetYTD)>}(Amount)) < Sum({$<$(vSetLYYTD)>}(Amount)),chr(9660),chr(9650))


Then, you can show your variance in a lot more elegant way if you simply attach the CHR() symbols to your positive and negative parts of the number format. For example, the following formula shows Year over Year Change %, with the appropriate arrow up or down. The first part of the numeric formatting is the "positive" format and the second part is the "negative" format.


NUM(

     Sum({$<$(vSetYTD)>} Amount) / Sum({$<$(vSetLYYTD)>} Amount) - 1,

     chr(9660) & '#,##0.0%;' & chr(9650) & '#,##0.0%'

)


You can learn this trick, along with many other advanced techniques, at the Masters Summit for Qlik. Or, you can also learn many of them from my new book QlikView Your Business.


Cheers,

Oleg Troyansky

chitemerere
Valued Contributor

Re: Variance arrows in QlikSense

Many thanks Oleg, thats perfect, can one show red down arrows and green up arrows?

Community Browser