Making NULL Selectable in Straight Table.

[#1]

Hi,

I have a straight table with a calculated dimension column. The column just does a simple count. However, for rows where the count is zero, a "-" (i.e., NULL) is displayed instead. How would I make the "-" display "0" instead and also make it selectable so that I can filter by only those rows that have a zero count?

Thanks,

Kenny

[#2]

Hi,

    Post some example.

[#3]

The expression for my calculated dimension (this is a column in a straight table) is:

=AGGR(Count(DISTINCT {<[Status Type] = {"CON"},[Action Type] = {"INS","UPD"}>}[Partner ID]),[Product Id])

So I see I can change the Null Symbol to whatever I want ("0" in this case). Now I just need to figure out how to make the zero selectable. Also, why can't I sort this column by descending/ascending order by double-clicking the column heading like I can for other dimensions? Thanks.

[#4]

Nevermind about the sort. I had no check boxes selected in the Sort tab for that dimension for some reason. Just need to figure out how to make NULL=0 and make it selectable.

[#5]

in the Presentation tab, you can display Null and missing values how you want and can change the '-' to '0'.

[Oleg_Troyansky]

Hi Kenny,

So, I think you already discovered by now that you can substitute the NULL symbol with any other symbol in the Presentation tab of the Chart Properties.

Generally speaking, NULL values cannot be selected, however in your case, selecting the NULL values in your calculated dimension will lead to selecting the corresponding Product IDs - the dimensions that you use in your AGGR. If you don't see it happening, please post a sample and we will take a look.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[#7]

Thanks, Oleg. Unfortunately, selecting the "0" does not select all corresponding product IDs with "0" (i.e., NULL) value. I found this syntax from set blanks field to numeric null i.e 0:

if(isnull(expression),0,expression)

Would you know how to incorporate this into the syntax I provided above somehow?

[Oleg_Troyansky]

Just like that - replace the word "expression" with your formula (remove the equal sign in front of the expression).

[#9 · Accepted solution]

if(isnull(AGGR(Count(DISTINCT {<[Status Type] = {"CON"},[Action Type] = {"INS","UPD"}>}[Partner ID]),[Product Id])),0,AGGR(Count(DISTINCT {<[Status Type] = {"CON"},[Action Type] = {"INS","UPD"}>}[Partner ID]),[Product Id]))

I believe this is correct - replace 'expression' with your actual expression in the 2 places

[#10]

Yes, that worked! Thanks so much for everyone's responses...really appreciate the help.