2 Replies Latest reply: Feb 14, 2018 8:12 AM by Anibal Martinez-Sistac RSS

    Cell colors changing with above or below function

    Anibal Martinez-Sistac

      I have a table with 2 fields:

      Avg Exchange Rate: calculated as avg(1/[Exchange rate])

      Date: set of dates in the format DD.MM.YY


      What I want is to set the cell color to red if the exchange rate in one day is equal to the exchange rate it had the day before. My solution works perfect in Qlik Sense desktop (attaching the app), but not well in Qlik Sense Hub.


      Mock Data for Data Load Editor:


        Load * inline


        Exchange rate,      Date        

        1.2222,             30.01.18,    

        1.2222,             29.01.18,    

        1.2222,             28.01.18,    

        1.2222,             27.01.18,    

        1.2655,             26.01.18,    

        1.4256,             25.01.18,    

        1.5647,             24.01.18,    

        1.2655,             23.01.18,

        1.2655,             22.01.18,

        0.8595,             21.01.18



      Then in "Edit Sheet", I have created a simple table with "Date" and "Avg Exchange Rate". I have gone to options of "Avg Exchange Rate" and in the option "Background color expression" have added this one:

      if( num((avg(1/[Exchange rate])),'#,##.0000') = below( num((avg(1/[Exchange rate])),'#,##.0000')), red(), white())


      It works perfect in Qlik Sense Desktop, nevertheless does not work in Qlik Sense Hub: Only when I have 6 decimal units it works good. Format pattern used: #,##0.0000000:

      6 decimals (Format pattern: #,##0.000000) -> Perfect

      But when I have 5, 7 or any other decimal points it does not work, and it is the same exchange rate number!, obtained from the same source:

      5 decimals (Format pattern: #,##0.00000):

      7 decimals (Format pattern: #,##0.0000000):


      Is there any other possible way to try to do this? I.e. another Qlik expression in "Background color expression" that achieves the same purpose? Thanks in advance for your help and any help or advice on what could be happening is appreciated, Anibal