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

# Formula to select the correct [Threshold] within a partition

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

• ###### 2. Re: Formula to select the correct [Threshold] within a partition

Hi Justin,

Need more clarity.

Best

Shriram Saxena

• ###### 3. Re: Formula to select the correct [Threshold] within a partition

Hi.

See if this is what you need.

1 of 1 people found this helpful
• ###### 4. Re: Formula to select the correct [Threshold] within a partition

Hi Luciano

This is very close to what I'm looking for (actually, it's exactly what I asked for) but is it also possible to populate the New Threshold with "FALSE" where there is a blank.

So, for example; ALERT1 would show TRUE for House bu ACCOUNT and TRADER would show as FALSE - meaning that [Entity Level] is fully populated?  If this is possible it would really help complete this for me.

Aside from the formula, which I can seem please would you also let me know how you solved this?  I have been spending some time trying to recreate your excellent solution so thought it would be good to understand anything that needs to be done behind the scenes.

Much appreciated!

J

• ###### 5. Re: Formula to select the correct [Threshold] within a partition

Can you fill an xls file wit exactly output you need?

• ###### 6. Re: Formula to select the correct [Threshold] within a partition

Imagine Tableau works with 1 line by time. It means that when you are reading House ACORN you can't now what is in Meta Market.

I jus duplicate the initial data twice: one for House ACORN and other for Meta Market, then i can evaluate House ACORN vs Meta Market or the opposite.

The trick is not close all keys.

1 of 1 people found this helpful
• ###### 7. Re: Formula to select the correct [Threshold] within a partition

Luciano Vasconcelos wrote:

Can you fill an xls file wit exactly output you need?

Sure - no probs.  I've provided some examples below.  The top rows show the data as it might appear before the New Threshold is applied and below each is shown how the output would look.  They all show that House ACORN's threshold takes precedence when it is different to Meta Market and what is also shown is that FALSE is the output where the threshold for both is blank.

Again - really appreciate your help.  Please let me know if you'd like me to clarify further.

• ###### 8. Re: Formula to select the correct [Threshold] within a partition

Justin, i asked a xls because language are not helping us.

Can you create a xls like below but completely filled?