4 Replies Latest reply: May 30, 2017 12:48 PM by William Christensen

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

William Christensen May 23, 2017 12:40 PM

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?

William Christensen May 23, 2017 12:48 PM (in response to William Christensen)

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.
Like Show 0 Likes (0)

Actions
- Re: Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?
  
  Marcus Sommer May 24, 2017 10:36 AM (in response to William Christensen)
  
  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
  
  Like Show 2 Likes (2)
  
  Actions
  - Re: Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?
    
    William Christensen May 25, 2017 9:39 AM (in response to Marcus Sommer )
    
    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!
    
    Like Show 0 Likes (0)
    
    Actions
- Re: Show nulls: How do I create dimension with Aggregate function wrapped in an If Statement?
  
  William Christensen May 30, 2017 12:48 PM (in response to William Christensen)
  
  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');
  
  Like Show 0 Likes (0)
  
  Actions

Go to original post