8 Replies Latest reply on Oct 6, 2016 5:32 AM by Luciano Vasconcelos

    Formula to select the correct [Threshold] within a partition

    justin.nathan.1

      Can anyone help me with a formula that would be able to select the correct [Threshold] (shown as TRUE / FALSE).  The rule that I need to create is as follows:

       

      • If the [Entity] is 'House ABC' then use it's related [Threshold] and ignore 'Meta Market' within that partition across TEST A, TEST B etc.
      • But if [Entity] 'House ABC' is NULL then use the [Threshold] from 'Meta Market' within that partition across all Tests

       

      Hope this is making sense - happy to explain in more detail but have been busting my ***** with this one even though I am sure that there's a simple solution using formula that I'm just not that familiar with.

       

      --------UPDATE-------

       

      I have attached a .twbx to help explain and clarify what is needed to be done here and have annotated points to highlight what the problem is.  Furthermore, I include the following high-level explanation / criteria to assist in getting the correct answer:

       

      • If the [Entity] is ‘ACORN’ then this [Threshold] will always take precedence over [Entity] ‘Meta Market’
        • For example, [Alert] ‘ALERT 3’ should show as being turned off at [Entity Level] ‘House’ because ‘ACORN’ is showing as ‘FALSE’ at [Entity Level

      BUT

       

      • If [Entity] ‘ACORN’ is showing blank / null [Threshold] then the [Threshold] for [Entity] ‘Meta Market’ should be reflected
        • For example, [Alert] ‘ALERT 1’ should show as being on at [Entity Level] ‘House’ because ‘ACORN’ is showing as blank / null

       

      Many thanks

       

      J