Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

Cell colors changing with above or below function

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

2 Replies

Re: Cell colors changing with above or below function

Seems like a bug to me, may be have mto‌ can advice or link to someone who might have an idea of what is going on

New Contributor III

Re: Cell colors changing with above or below function

Hi Sunny,

My Qlik version is 3.2.

Do you know how can I contact mto to try to solve this weird case of Qlik Sense Hub?

For example, another case, if I put my working example from Qlik Sense Desktop in Qlik Sense Hub, the expression only works for the day 10.02.18., which is OK because Avg Exchange rate is the same as the previous day; but it is not working for the day 13.02.18., which should be marked in dark red as well as the Avg Exchange rate from 13.02.18. is the same as the previous day as well. In desktop it works fantastic.  


Thanks in advance,