Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

lwestmaas
New Contributor III

Suppress dimension if measure returns null but NOT 0

Hi all,

 

We have business units that have closed, so their values for the current year are obviously 0 since they do not exist. There are other units that DO exist in the current year but have actual 0 values.

 

Values for closed business units return as null (they are percentages being divided by 0), but values for true 0s return as 0.

 

I know I can use the "Include Zero Values" check box to remove both. How can I remove ONLY dimensions that return null values and not dimensions that return true zero?

 

The only solution I've come up with is load a separate dimension that excludes closed business units; this is not ideal because I want the closed units to display if a user wants to look at data from previous years.

 

Thank you!

 

Lucas

Labels (2)
9 Replies
Digvijay_Singh
Honored Contributor III

Re: Suppress dimension if measure returns null but NOT 0

Did u try unchecking 'Include null values' in the dimension properties?

Or may be {<DimensionField={"*"}>} in the measure expression

lwestmaas
New Contributor III

Suppress dimension if measure returns null but NOT 0

That doesn't work, unfortunately. The issue is that the dimension is not null but the output of the measure is.
Digvijay_Singh
Honored Contributor III

Re: Suppress dimension if measure returns null but NOT 0

You may try this may be in the measure-
If len(Measure)>0, Measure)
Highlighted
vikasmahajan
Esteemed Contributor

Re: Suppress dimension if measure returns null but NOT 0

Hi,

Have you try with un-tick include zero values option ?

Vikas


untick-include zero values.jpg

Partner
Partner

Re: Suppress dimension if measure returns null but NOT 0

Hi,

Maybe it will be easier to flag your data in the script when its closed business units and use the flag value to show (or not) your dimension in the chart.

 

That's what i always use.

lwestmaas
New Contributor III

Suppress dimension if measure returns null but NOT 0

Can you be more specific? Do you mean the flag would be contained as a set analysis in the measure? Unless I'm misunderstanding that won't resolve the issue; the measures are already resolving to null values for the closed units.
Partner
Partner

Re: Suppress dimension if measure returns null but NOT 0

hi again,

No, prepare the data in the load script.

Ex :

Load *, if([Business Unit]= 'X' and DATE_Closed>=today() , 1, 0) as Flag_Open_Close

Resident MyTable;

Then, you could use this flag as a shown condition in your dimension 

Ex: Flag_Open_Close=1

 

 

lwestmaas
New Contributor III

Suppress dimension if measure returns null but NOT 0

What do you mean by "shown dimension"? To be clear, this issue arises when I am breaking data out by current region - e.g. "Region 1", "Region 2", and "Closed". Thus I am already using a dimension and in some cases due to chart type/options cannot add a second dimension. This is not an issue with data from closed units affecting whole-system calculations.
Partner
Partner

Re: Suppress dimension if measure returns null but NOT 0

Would something like this work in the dimension field:

IF(Not IsNull(Measure),Dimension value)

Then make sure Include Null Values is unchecked.