topic Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement? in App Development

Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?

wdchristensen — Tue, 23 May 2017 16:30:45 GMT

I have an application that uses approximately 100 application variables. Some of my variables are “true / false” evaluation conditions for reuse across multiple set analysis formulas. For example, I want to count the number of sales in North America that occurred by month, excluding all sales that occurred on a holiday. I have several other visualizations that will use these same conditions so I put them in an application variable as follows:

VariableName: VariableDefinition

vHoliday: Holiday={'N'}

vMarket: Market={"North*"}

vSalesID: SalesID

I would like to create a download page that shows all my “true / false” set analysis conditions as a boolean values based on the evaluation of the condition. The issue is that set analysis syntax does not always correspond to an “if statement”. So if I want to know the value of vHoliday, I can rewrite the variable as follows and determine the result.

=If(Market='North Texas', 'TRUE','FALSE')

=If($(vMarket)=TRUE, 'TRUE','FALSE') /* Incorrect syntax */

Rewriting the formula defeats the purpose of reusability so my question is how can I extract the value of the field? I was thinking of creating a dimension (since there is no show null values on measures) as follows:

IF(SUM({<$(vMarket)>} 1) >0, ‘True’, ‘False’)

I keep getting an invalid dimension error. Any help would be greatly appreciated!

Re: Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?

wdchristensen — Tue, 23 May 2017 16:48:12 GMT

I created a Measure that I forced to show null values with the formula below:

=if((SUM({<$(vMarket)>} 2)) - (SUM({<1=1>} 1))=1,'True','False')

This works but seems sloppy.

Re: Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?

marcus_sommer — Wed, 24 May 2017 14:36:17 GMT

I'm not sure that I understand what do you want to do but you could try something like this:

set eCheck = if(SUM({<$1>} 1), dual('True', true()),dual('False', false()));

and then as expression:

$(eCheck($(vMarket)))

- Marcus

Re: Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?

wdchristensen — Thu, 25 May 2017 13:39:26 GMT

Thanks so much for your response it was exceedingly helpful!

In a dimension you have the ability to "Show Nulls" but on a measure there is no such feature (to my knowledge). So essentially I want to create a download page that shows an individual conditions of the set analysis as individual fields so I can show the users why a visualization my be contains the data it does. In some cases the users feel a record should be included and my goal is to be able to answer the "Why is record X not included in Visualization Y?". By displaying each true / false condition as it's own field in a datatable at the bottom of the app I can quickly answer those questions. The individual T/F conditions already reside in variable so if I plug them into the eCheck "function" you created, then I can easily see the building block of the end result which may have up to a dozen conditions.

The awesome part is that even though I didn't go a great job of explaining my issue, your solution worked perfect!

Re: Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?

wdchristensen — Tue, 30 May 2017 16:48:49 GMT

The logic above has some issues. The logic below seems to work.

SET fxEvaluate = if((SUM({<$1>} 1)) - (SUM({<1=1>} 0))>=1, 'True', 'False');