3 Replies Latest reply on Dec 1, 2016 3:27 PM by Michel Caissie

Calculated fields with partitions (turn cbet)

Dear Tableau Community,

I am trying to count the occurrences of certain data patterns with a calculated field. For example, in the attached workbook I want to count the number of times that a 'player' has a 'prf action' = 'r' OR '3b' AND a 'flop action' of 'r' OR '3b' AND a 'turn action' of 'r' OR '3b' then divide this number by all the times that a 'player' had a 'prf action' = 'r' OR '3b' AND a 'flop action' = 'r' OR '3b'

But I need my calculated field to look for this pattern in each 'hand no'. In other words, I need all of the IF / AND / OR criteria to be met with in a single 'hand no' in order for the statement to be true.

The above should make sense if you can view my workbook but I've added some explanation below to help anyone who can't open the workbook.

NOTES:

+ 'player', 'prf action', 'flop action', 'hand no.', and 'turn action' are all columns in my data set

I have put together the below calculated field but it doesn't address 'hand no' and thus doesn't produce the results I am looking for.

SUM(IF

[Prf Action] = 'r'

OR

[Prf Action] = '3b'

AND

[Flop Action] = 'r'

OR

[Flop Action] = '3b'

AND

[Turn Action] = 'r'

OR

[Turn Action] = '3b'

THEN 1

ELSE 0

END)

/

(SUM(IF

[Prf Action] = 'r'

OR

[Prf Action] = '3b'

AND

[Flop Action] = 'r'

OR

[Flop Action] = '3b'

THEN 1

ELSE 0

END))

Any and all help is appreciated!

Best,

Sam

• 1. Re: Calculated fields with partitions (turn cbet)

Samuel,

Be carefull when mixing AND and OR  in a logic statement. Parenthesis are often needed and I think that it is what's missing in your case.

Is the attached what you are looking for ?

Michel

1 of 1 people found this helpful
• 2. Re: Calculated fields with partitions (turn cbet)

Hi Michel,

I've played around with the data and it appears to be doing what I want now. Thank you.

I do have one question, since the calculated field does not address hand no, how is that the formula doesn't count it as true when a player meets the criteria for prf action and flop action in one hand and then meets the turn action criteria on another hand?

For example, the player 狼太狼 meets the prf action and flop action criteria in hand 2, and then meets the turn action criteria in hand 1. I would have thought that without addressing hand no in the formula this would then result in all three criteria being met.

Is it the case that tableau is by default only checking the data for each entry (row) as opposed to checking for the criteria across all entries (rows)?

Apologies if this is a rudimentary question, I just don't fully grasp why this works.

Thanks again!!!

Best,

Sam

• 3. Re: Calculated fields with partitions (turn cbet)

If you look on the sheet ValidateData, you can see that Tableau computes the logic for every Row , then aggregate the results based on the dimension you have in the view, in your case Player (sheet cbet turn).

If you would need to answer the question  - does the player have at least one hand with Prf Action = r   you could use the formula

{FIXED Player: MAX(if [Prf Action] = 'r' then 1 else 0 end)}

so if the player have no raise on preflop in any hand  the MAX would be 0  and if it have  in 1 or more hands  , the max would be 1.