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

Calculated Field similar to Count if on Dynamic Bar Chart

Eric Renner Aug 13, 2019 9:08 AM

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: added clarification on percentages

Edit: typos

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

Jim Dehner Aug 13, 2019 8:06 AM (in response to Eric Renner)

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.
Like Show 0 Likes(0)

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

Eric Renner Aug 13, 2019 8:51 AM (in response to Jim Dehner)
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

Book5.twbx 16.4 KB
Like Show 0 Likes(0)

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

Jim Dehner Aug 13, 2019 8:35 AM (in response to Eric Renner)
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

if the need is something else please help me understand

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.

Book5.twbx 16.7 KB
Like Show 0 Likes(0)

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

Eric Renner Aug 13, 2019 9:01 AM (in response to Jim Dehner)

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
Like Show 0 Likes(0)

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

Soumitra Godbole Aug 13, 2019 8:50 AM (in response to Eric Renner)

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
Like Show 0 Likes(0)

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

Eric Renner Aug 13, 2019 8:51 AM (in response to Soumitra Godbole)

Thanks Soumitra. I have fixed it.
Like Show 0 Likes(0)

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

Eric Renner Aug 13, 2019 10:03 AM (in response to Jim Dehner)

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
Like Show 0 Likes(0)

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

Soumitra Godbole Aug 13, 2019 10:13 AM (in response to Eric Renner)

Hi Eric,

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

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

Eric Renner Aug 13, 2019 10:36 AM (in response to Soumitra Godbole)
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.

Book1.xlsx 10.0 KB

Book Test.twbx 16.3 KB
Like Show 0 Likes(0)

Actions