color condition when there is no data

[doespirito]

Hello,

here is a crosstab that counts the number of subscriptions (count(distinct Numéro_enregistrement))

I defined a color condition for the lines :  if(Type_livraison='Autres',blue(),red())

1st question : how to put 0s in place of '-'

2d question : how to ensure that the blue color condition applies to the entire width of the line, including when the formula does not return any data?

Thank you very much for your help !

Arnault

[CHRIS_Singa]

You can use CSS Style as a solution for your problem

[marcus_sommer]

The cause of your observed behaviour is that there are no data are available for the queried field-combination respectively they are just NULL. NULL isn't stored and couldn't be accessed in any way.

A common workaround for it is to replace the NULL with real values which might be done on a fieldvalue-level with the NULL variables or a NULL mapping or directly within the loads with something like:

if(len(trim(MyField)), MyField, '<NULL>') as MyField

If it's not on field-level else on a record-level these records should be populated.

Be aware that NULL isn't an error else a feature which has also benefits. Therefore your wanted replacement of '-' with ZERO and the shown coloring will have own disadvantages and may not be the most expedient approach. 

[doespirito]

thank you for your answer but these are not records which contain '<null>' but the result of a calculation (distinct count) which returns 0 because indeed there is no subscriber meeting the criteria in the table. For me 0 is the correct result.

[lennart_mo]

In that case you should try the alt() function:

Alt(count(distinct Numéro_enregistrement), 0)

With this, if your count() returns NULL, Alt() replaces it with 0.

[CHRIS_Singa]

Tu peux forcer la valeur pour que tu puisses avoir de zéro à cette cellulle.

Je te laisse regarder cette vidéo : https://www.youtube.com/watch?v=lTf2Q6Rc7LM&ab_channel=Thinkmetrics

D'autres fonctions peuvent aider : 

Fonction ALT

Fonction ISNULL

Fonction EMPTYISNULL

[marcus_sommer]

I do understand that you expect ZERO as the right result but in regard to the existing data and their associations to each other is NULL more correct.

It's more obvious if we slightly change the calculation from a count() to a sum(). A count() couldn't be negative and even a real and correct ZERO is quite seldom but a sum() could go in each direction and of course resulting in a valide ZERO. If now with any measurement a NULL is transformed into ZERO you couldn't directly differentiate between them anymore. This may cause any mis-interpretation of the data or some kind of confusion. Personally I prefer to show the whole TRUTH (of missing data and/or a poor data-quality) instead of cleaning the data - as it is usually wanted from the business.

Nevertheless you may adjust the data-model like I hinted above or also trying (it's depending on the data + data-model + object-dimensionality) a simple approach like:

count(distinct MyField) + sum({1} 0)

to enforce a ZERO replacement.

[doespirito]

Thanks for your help. Unfortunately none of your solutions work. I am attaching a demo app in case inspiration strikes.
Thanks again

[gomeri]

Hello,

 you can't color null values in the cell from condition in Qlik Sense measure.

I suggest you to rename null cells in script editor by inserting this row:

IF(LEN(TRIM(Livraison))>0, Livraison, '-') AS LivraisonNorm

And then apply condition for background of table: 

IF((LivraisonNorm='-'), RGB(248,203,173))

 or

IF((LivraisonNorm='portage'),rgb(248,203,173), rgb(248,203,173)) 

Thanks