Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'd like to colour the rows in my cross-table with white and grey stripes.
In order to do so I apply the following formula for the background colour: if(mod(RowNo(),2)=0,RGB(242,242,242))
But this method of colouring doesn't work for those cells where the value is missing. See the file attached.
Is there another way to colour a cross table with white and grey stripes?
Thank you in advance,
Larisa
Try deselecting "Supress Zero Values"
Hi,
'Supress zero-values' has been already deselected. It doesn't solve the problem.
There are two types of null vaues: nulls and missings. Nulls are displayed as nulls in my cross-table and missings are displayed as '-'. That's correct. Supress zero-values is unchecked.
Try:
if( isnull( <Expression>), 0, <Expression>)
So you replace missing with 0
You could try to apply your background-color (additionally) to the custom cell format. Another approach could be to try to set the cell-value to 0 maybe with: alt(YourExpression, 0).
- Marcus
I like the idea of using the custom cell format. But can't understand why it fail to work. As you can see in the attached file, there's a field expression in the custom cell format area. If I type the expression and then qlik 'apply', QV automatically clears my expression. Is it a bug?
As for another idea: I need to show the symbol '-' for the end user, so I can't convert '-' to 0.
I could imagine that it isn't possible to use a calculated expression within the custom cell format and qv had forgotten to disable these option which is now confusing
Maybe dual() could be used to show '-':
dual(replace(alt(YourExpression, 0), 0, '-'), alt(YourExpression, 0))
- Marcus
Thanks, Marcus!
Is it possible to correct your formula taking into account that not all NULLs shoud be converted to '-'?
Actually, in QV there 2 types of missing values.
1) blank strings
2) and real nulls (the result of a calculated expression)
I tried your formula. It converts both types of missings into '-'. But I don't need to convert the second type of missings to '-'.
Is it possible to correct your formula so that real nulls (the second type) could not be converted to '-'?
Maybe so:
dual(if(isnull(YourExpression), 'NULL',
if(len(YourExpression) = 0, 'MISSING',
YourExpression)),
alt(YourExpression, 0))
- Marcus
Thank you, Marcus!
But it seems that while using the isnull-function inside the if-function it is not possible to set the if-false-part of the if-function.