2 Replies Latest reply: Jan 2, 2016 5:34 PM by Christopher Chitemerere RSS

    Variance arrows in QlikSense

    Christopher Chitemerere

      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.

        • Re: Variance arrows in QlikSense
          Oleg Troyansky

          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