topic Re: If statement for dimension, exclude fields, handle nulls in App Development

If statement for dimension, exclude fields, handle nulls

evansabres — Wed, 08 Apr 2020 20:13:26 GMT

I have a field, 'Group' that I am using as a dimension. I am excluding certain values from the dimension, such as 'ABC', 'DEF', by =IF(Not Match(Group, 'ABC', 'DEF), Group) however, would there be a way to introduce more logic to rename fields that are blank or null?

Re: If statement for dimension, exclude fields, handle nulls

sunny_talwar — Thu, 09 Apr 2020 12:15:24 GMT

Something like this?

=If(Len(Trim(Group)) = 0, 'BLANK', If(Not Match(Group, 'ABC', 'DEF), Group))

Re: If statement for dimension, exclude fields, handle nulls

evansabres — Thu, 09 Apr 2020 13:32:24 GMT

The structure of the calculated dimension works, however when I apply to my chart, the value still returns as '- '

Re: If statement for dimension, exclude fields, handle nulls

sunny_talwar — Thu, 09 Apr 2020 14:10:19 GMT

Is there a dash before the blank space?

Re: If statement for dimension, exclude fields, handle nulls

evansabres — Thu, 09 Apr 2020 14:12:12 GMT

My reply above is how my chart is displaying the row with the null or blank values:

'- ' I was just attempting to demonstrate that the dash is on the left of the cell,if that is of any significance.

Re: If statement for dimension, exclude fields, handle nulls

sunny_talwar — Thu, 09 Apr 2020 14:14:07 GMT

Can you show a screenshot to show the issue?

Re: If statement for dimension, exclude fields, handle nulls

evansabres — Thu, 09 Apr 2020 14:24:24 GMT

Re: If statement for dimension, exclude fields, handle nulls

sunny_talwar — Thu, 09 Apr 2020 14:32:07 GMT

I think this null is related to if condition not Match(Group, 'ABC', ...)... in order to get rid of this, uncheck 'Show zero value' under your dimension

Re: If statement for dimension, exclude fields, handle nulls

evansabres — Thu, 09 Apr 2020 14:33:56 GMT

I want to show the blank field as it is important to the data in this application. I want to rename the blank field something like 'No Preference'

Re: If statement for dimension, exclude fields, handle nulls

sunny_talwar — Thu, 09 Apr 2020 14:36:45 GMT

Try this

=If(Not Match(Group, 'ABC', 'DEF), Group, 'No Preference')

Re: If statement for dimension, exclude fields, handle nulls

evansabres — Thu, 09 Apr 2020 14:41:15 GMT

This works for the structure of the equation, however as the field is Null, it is still showing as Null/Blank in my table.

Re: If statement for dimension, exclude fields, handle nulls

sunny_talwar — Thu, 09 Apr 2020 15:12:55 GMT

May be try this

=If(Not Match(Group, 'ABC', 'DEF) and Len(Trim(Group)) > 0, Group, 'No Preference')

or this

=If(Len(Trim(Group)) = 0, 'No Preference', If(Not Match(Group, 'ABC', 'DEF), Group, 'No Preference'))