12 Replies Latest reply on Jul 19, 2016 8:07 AM by Purvi Ajmera

    calculated field

    Purvi Ajmera

      Hello,

       

      I'm trying to use a calculated field to avoid some back end work I've been doing in excel.

       

      Goal:  Create a calculated field called "Type of Pipeline"  with these four rules and would want to be able to group them so I can create a drop down filter by type of Pipeline.  (like screen shots below) Hope that makes sense.

       

      IF [Percent Complete] > -1 and [Confidence %] > -1 THEN "Full Pipeline"

      IF [Percent Complete] > 25  and [Confidence %] > 25 THEN ">25% Probability"

      IF [Percent Complete] > 45 and [Confidence %] > 45 THEN ">50% Probability"

      IF [Percent Complete] > 70 and [Confidence %] > 70 THEN ">75% Probability"

       

       

        • 1. Re: calculated field
          Jeremiah Piescik

          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

            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

             

             

            • 3. Re: calculated field
              Jeremiah Piescik

              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

                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 record-level 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

                  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

                    Hi Jonathan,

                     

                    Yes you are correct- I need the record to populate in multiple pipeline

                    • 7. Re: calculated field
                      Jonathan Drummey

                      Hi Purvi,

                       

                      Are you still looking for help with this?

                       

                      Jonathan

                      • 8. Re: calculated field
                        Purvi Ajmera

                        yes please if you have the time

                        • 9. Re: calculated field
                          Jonathan Drummey

                          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

                            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

                              Here's a revised solution, this is a good example of how we need to switch from spreadsheet-based-thinking to Tableau-thinking 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 record-level 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:

                               

                              Screen Shot 2016-07-18 at 4.49.26 PM.png

                               

                              If we put all those on Rows then we end up with a bunch of bar charts:

                               

                              Screen Shot 2016-07-18 at 4.50.18 PM.png

                               

                              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 record-level 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:

                               

                              Screen Shot 2016-07-18 at 4.55.07 PM.png

                               

                              workbook is attached.

                               

                              Jonathan

                              • 12. Re: calculated field
                                Purvi Ajmera

                                Thank you for putting time to help with this....much appreciated Jonathan!

                                 

                                Thank you!