
1. Re: calculated field
Jeremiah Piescik Jul 6, 2016 11:44 AM (in response to Purvi Ajmera)Hi Purvi,
A more Tableauish syntax for your calculated field would be:
IF [Percent Completed] > 0.01 AND [Confidence %] > 0.01 THEN STR('Full Pipeline')
ELSEIF [Percent Completed] > 0.25 AND [Confidence %] > 0.25 THEN STR('25% Probability')
ELSEIF [Percent Completed] > 0.45 AND [Confidence %] > 0.45 THEN STR('50% Probability')
ELSEIF [Percent Completed] > 0.70 AND [Confidence %] > 0.70 THEN STR('75% Probability')
END
After that you will be able to drag the field to the filters shelf > right click > select show filter.

2. Re: calculated field
Purvi Ajmera Jul 6, 2016 1:55 PM (in response to Jeremiah Piescik)Thanks Jeremiah but it didn’t solve my problem. Everything falls in Full Pipeline which is right but portion of that should be in other buckets as well…I want my end result to be as screen shot below. I’m attaching my workbook if it helps you understand

TRIAL for One click.twbx 7.1 MB

image001.png 21.5 KB


3. Re: calculated field
Jeremiah Piescik Jul 7, 2016 1:26 AM (in response to Purvi Ajmera)Yes the workbook helped. I have reattached it with a calculated field called "Pipeline Type". Additionally I added a sheet called "Using Calculated Field" in which I created a vis for you using the new calculated field and sum of expected Revenue... From there you should be able to continue to slicing the data as desired to replicate the visual as desired. Let me know if you require additional help.

4. Re: calculated field
Jonathan Drummey Jul 6, 2016 7:20 PM (in response to Purvi Ajmera)It looks like are two problems here:
1) The calc is possibly written in the wrong order, Tableau evaluates from the start of an IF/THEN to the END to bottom so anything that passes the first test is not evaluated for other tests.
2) A recordlevel calculated field only returns a single result per record, it appears that you're trying to have the calc return multiple results per record? If a record has [Percent Completed] = 0.5 and [Confidence %] = 0.5 then it seems like you at least one it to show in the Full Pipeline *and* the 50% Probability, is that the case? Also, is it supposed to show in the 25% Probability as well?
Jonathan

5. Re: calculated field
Purvi Ajmera Jul 7, 2016 9:29 AM (in response to Jeremiah Piescik)If a record includes in multiple pipeline type it does not capture that....
>75% amount is correct but rest excludes what is already in other pipelines
so >50% would be $$ in >50% plus >75%
>30% would be $$ in >30% plus >50% plus >75%
So should I be grouping them??
I almost want to go back to excel...

6. Re: calculated field
Purvi Ajmera Jul 7, 2016 9:32 AM (in response to Jonathan Drummey)Hi Jonathan,
Yes you are correct I need the record to populate in multiple pipeline

7. Re: calculated field
Jonathan Drummey Jul 14, 2016 7:52 AM (in response to Purvi Ajmera)Hi Purvi,
Are you still looking for help with this?
Jonathan

8. Re: calculated field
Purvi Ajmera Jul 14, 2016 12:44 PM (in response to Jonathan Drummey)yes please if you have the time

9. Re: calculated field
Jonathan Drummey Jul 15, 2016 7:55 AM (in response to Purvi Ajmera)What is your actual data source? Excel, Oracle, ?? The reason that I ask is that the easiest solution won't work on all data sources.
Jonathan
Sent from my iPhone

10. Re: calculated field
Patrick A Van Der Hyde Jul 15, 2016 2:19 PM (in response to Purvi Ajmera)moved frm Job listings to Forums. not sure how you had this post end up in the Job Listings section.

11. Re: calculated field
Jonathan Drummey Jul 18, 2016 2:04 PM (in response to Purvi Ajmera)Here's a revised solution, this is a good example of how we need to switch from spreadsheetbasedthinking to Tableauthinking when we start using Tableau. In Excel we can have a set of data and arbitrarily sum up cells in that data via whatever cell references we want. So once a row of data has been identified as belonging to a condition such as being at a certain probability level, for example by using a SUMIF() function, then we can include that in a resulting cell. And we can quickly generate additional result cells that refer to that same row so anything with a 75+% probability can be counted in the 50+ and 25+% probability as well.
Tableau doesn't do arbitrary cell references, instead Tableau thinks like a database. So when we build a calculation in Tableau we are effectively adding a new column to the data where each record will have one and only one result value of that calculation. So a calculation like this one:
IF [Percent Completed] > 0.01 AND [Confidence %] > 0.01 THEN STR('Full Pipeline')
ELSEIF [Percent Completed] > 0.25 AND [Confidence %] > 0.25 THEN STR('25% Probability')
ELSEIF [Percent Completed] > 0.5 AND [Confidence %] > 0.5 THEN STR('50% Probability')
ELSEIF [Percent Completed] > 0.75 AND [Confidence %] > 0.75 THEN STR('75% Probability')
END
That is computed row by row (aka record by record) will only return a single value for each record. Since this calculation uses an IF/THEN statement Tableau starts at beginning of the formula and will stop once it reaches the first TRUE condition, if there aren't any TRUE conditions then it will use the ELSE, and finally if there are no TRUE conditions and no ELSE then it will return Null.
So if a record has a 75% Percent Completed & 75% Confidence then the calculation is going to return only the "25% Probability" string for that record, *not* count the record in the desired 25%, 50%, and 75% Probability buckets.
Therefore to count in multiple Probability buckets we need to do something similar to what we did in Excel, we need to create fields for each of the buckets. Here's the formula for the "25% Probability" measure:
IF [Percent Complete] > 25 AND [Confidence %] > 25 THEN
[Expected Revenue]
END
And here's the formula for the 50% Probability measure:
IF [Percent Complete] > 50 AND [Confidence %] > 50 THEN
[Expected Revenue]
END
These calculations are recordlevel calculations that will have the default SUM() aggregation applied, so they work like a SUMIF() in Excel. Note that I used the Expected Revenue measure here because I didn't actually know what measure you wanted, you could replace that with any other measure in each calculation.
Now that we have the measures, the next step is to build the view. If we simply drop all of them on the Text Shelf we end up with something that looks a bit messy like this:
If we put all those on Rows then we end up with a bunch of bar charts:
What we really want to do is treat these several measures like members of a single dimension so we can end up with a text table or bar chart. When we want to turn measures into a dimension there are basically three techniques in Tableau:
 Use Measure Names/Measure Values
 Use the pivot feature to pivot recordlevel measures to create a dimension
 Use a Tableau data blend where the primary source is a "scaffold" of the dimensions that we need, the secondary is the original data, and a calculated field using a CASE or IF/THEN statement to return the right values of the secondary for the primary
In this case I used Measure Names/Measure Values because that is the easiest. What Measure Names/Measure Values does is to "pivot" a set of measures (the Measure Values) so that they are members of a fake "Measure Names" dimension. We can build a Measure Names/Measure Values table using Show Me, entirely through drag and drop. Here's the one I built for this post:
workbook is attached.
Jonathan

TRIAL for One click jtd.twbx 7.1 MB


12. Re: calculated field
Purvi Ajmera Jul 19, 2016 8:07 AM (in response to Jonathan Drummey)Thank you for putting time to help with this....much appreciated Jonathan!
Thank you!