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

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

    William Christensen

      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!