9 Replies Latest reply on Aug 13, 2019 10:36 AM by Eric Renner

# Calculated Field similar to Count if on Dynamic Bar Chart

Hello,

I am trying to figure out how to do a countif in Tableau Desktop 19.1.6

Basically I have 4 goods. For simplicity we will call them 1, 2, 3, & 4. I am currently totally them on the date they were produced = CDAT onto a Stacked Barchart. That works great. Problem is a certain percentage break after X number of days before their end of life. I want the bar chart to be able to adjust the totals based on a percentage of "still working" that I can manually enter. I have created a Parameter that displays in Percentage = A Percent Working. I am having a hard time coming up with the calculated field to use. Here is an example data set.

On 1/1/2000 product 1 was produced 10 times.

On 1/2/2000 product 2 was produced 8 times.

On 1/2/2000 product 3 was produced 20 times.

Over the live span of product 1, 90% will not break and will complete their jobs. Product 2, 3, & 4 all have different percentages.

I want to be able to input 90% into my parameter (1 Percent Working) and for the totals to adjust to:

On 1/1/2000 Product 1 = 9 remain

On 1/2/2000 Product 1 = 7 remain (rounded to whole number from 7.2)

On 1/3/2000 Product 1 = 18 remain

This must also work for 2, 3, & 4 on my stacked Bar Chart.

I've tried the following:

this works but I only want product 1 not 1,2,3,& 4

ROUND(COUNT([Cdat])*[1 Percent Working],0)

this one fails because I am mixing an Aggregate with a non-aggregate...which I don't understand

IF ([Product])=1 THEN ROUND(COUNT([Cdat])*[1 Percent Working],0) END

Any help would be appreciated and I'll try to answer any questions that I can. And, my company will not allow me to share data, but I can generate more fake data if needed.

Thanks,

Eric

Edit: typos

• ###### 1. Re: Calculated Field similar to Count if on Dynamic Bar Chart

good morning

yes a workbook with dummy data would make this easier

your agg/nonagg issue is that at least    COUNT([Cdat]) is an aggregate and ([Product])  is not == is product a measure (numeric? )  it thought it was a dimension String value?  but the formula is equating it to 0

if it is a string     then you can use attr(product), or min(product), or max(product)  also if it is a string you have to compare it to a string not a number

JIm

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculated Field similar to Count if on Dynamic Bar Chart

Good morning Jim,

See attached workbook. I have changed Products from A,B,C,D to 1,2,3,4. Let me know if you have more questions.

Basically: Product 1 has a 90% chance of working

Product 2 is 50% chance

Product 3 is 70%

Product 4 is 75%

So 1/1/2000 would go from

1=3

2=5

3=7

4=3

to

1=3 (because round(3 * 90%,0) = 3)

2=3 (because round(5 * 50%,0) = 3)

3=5 (because round(7 * 70%,0) = 5)

4=2 (because round(3 * 75%,0) = 2

Thanks,

Eric

Edit: added percentages of product working

edit: typo in 4=2

• ###### 3. Re: Calculated Field similar to Count if on Dynamic Bar Chart

I guess I'm just confused - you have Customers Identified as Alpha the only one in the data is A and products identified as 1 -4

if you are trying to use the parameter to adjust the values for Customer A and n other customer then this should work Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Calculated Field similar to Count if on Dynamic Bar Chart

Jim,

Your confusion is my mistake. Customer is irrelevant and the Products are not Alpha but they are Numeric. Does that help?

I want to adjust the values on the Products (1,2,3,4) not the customers.

Eric

• ###### 5. Re: Calculated Field similar to Count if on Dynamic Bar Chart

Hi Eric,

Just a small typo in the example you explained earlier:

4=3 cannot be right as 2.25 gets rounded to 2 and not 3

It should be 4=2 (because round(3 * 75%,0) = 2

Thanks !

Soumitra

• ###### 6. Re: Calculated Field similar to Count if on Dynamic Bar Chart

Thanks Soumitra. I have fixed it.

• ###### 7. Re: Calculated Field similar to Count if on Dynamic Bar Chart

Jim,

The products are 1, 2, 3, 4 and those are what I want to adjust.

This is close to what I want, however when I change my Prod 1 Working Percentage parameter, the values do not change.

IF [Product]=1

then [Prod 1 Working Percentage]*{ FIXED [Product],[Cdat]:COUNT([Cdat])}

END

Thanks,

Eric

• ###### 8. Re: Calculated Field similar to Count if on Dynamic Bar Chart

Hi Eric,

Would you by any chance have a dummy dataset (excel/csv/txt) ?

• ###### 9. Re: Calculated Field similar to Count if on Dynamic Bar Chart

I think I got it.

I created 4 Parameters: Par Prod 1, Par Prod 2, Par Prod 3, Par Prod 4

I then created a Calculation: Calculation 1: { FIXED [Product],[Cdat]:COUNT([Cdat])}

I then created a 2nd Calculation: Calculation 2:

if [Product]=1 then [Calculation1]*[Par Prod 1]/100/[Calculation1]

ELSEIF [Product]=2 then [Calculation1]*[Par Prod 2]/100/[Calculation1]

ELSEIF [Product]=3 then [Calculation1]*[Par Prod 3]/100/[Calculation1]

ELSEIF [Product]=4 then [Calculation1]*[Par Prod 4]/100/[Calculation1]

END

Now if I adjust any of the Pars it dynamically only adjust that Product in the Bar Chart. I still need to roll this into my actual file rather than the test one, but I've attached my test one here.