Variance arrows in QlikSense

[Anonymous]

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:

What could be wrong with this?

Regards.

[Oleg_Troyansky · Accepted solution]

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

[Anonymous]

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