10 Replies Latest reply on Jan 11, 2016 11:08 AM by Joe Oppelt

    Another Calculated Field Question

    Chrissy Scott

      Hi All,

       

      I'm hoping you can help me figure out a few calculated fields. My issue is that I have 57 records, but only 43 unique records. 14 records affected multiple LOBs.

      What the numbers come down to are:

      • There are 57 records total
      • There are 43 unique records
      • 12 of them are BIE (red)
      • 31 of them are BII (blue)

       

      So what I want in the tool tip (using FTS, the second line) as the example, is:

           FTS Details:

           Total for LOB: 12 (this calculation I have)

           <LOB BIE> out of <Total BIE> - or - 2 out of 12

           <LOB BII> out of <Total BII> - or - 10 out of 31

       

      I can't figure out any of those 4 calculated fields.

       

      Workbook is attached. Any help will be greatly appreciated.

       

      Thanks

        • 1. Re: Another Calculated Field Question
          Carl Slifer

          Howdy Chrissy,

           

          If I am correct you wanted to get a value that is the sum of the entire row in this case, the alias FTS in the LOB Field, which would be 12. It's divided into Impact Type which is 10 BII and 2 BIE

          In order to get the 'Total BII' use this:

          {EXCLUDE LOB :SUM(IF [Impact Type] = 'BII' THEN 1 ELSE 0 END)}

          OR

          {FIXED [Impact Type] :SUM(IF [Impact Type] = 'BII' THEN 1 ELSE 0 END)}

          AND

          {EXCLUDE LOB :SUM(IF [Impact Type] = 'BIE' THEN 1 ELSE 0 END)}

          OR

          {FIXED [Impact Type] :SUM(IF [Impact Type] = 'BIE' THEN 1 ELSE 0 END)}

           

          Using fixed will make it always return the # of impact type with respect to if its BIE or BII while using EXCLUDE only removes LOB from the calculation. They both work in the current use case. You then just have to make your tool tip formatted accordingly with the right calculations showing in the view.

           

           

          If I can be of more assistance please let me know.

           

          Cheers!

          Carl Slifer

          InterWorks

          • 2. Re: Another Calculated Field Question
            Joe Oppelt

            Here's an example using table calcs without using LOD.

            1 of 1 people found this helpful
            • 3. Re: Another Calculated Field Question
              Joe Oppelt

              100 ways to skin the cat.

              • 4. Re: Another Calculated Field Question
                Chrissy Scott

                Hi Joe,

                 

                How would I make the total tickets calculation exclude duplicates. You can see in the table below that there are several duplicates. I'm trying to get the total ticket count to only count each Call_Number once. Further, the LOB BIE Total and LOB BII Total would need to count each Call_Number once. Hope that makes sense...

                 

                  

                Call_NumberImpact Type
                2627578BII
                2591876BIE
                2591876BIE
                2591491BIE
                2591491BIE
                2594370BII
                2594725BII
                2596320BIE
                2596320BIE
                2598178BII
                2599772BIE
                2599772BIE
                2599782BII
                2600692BIE
                2600692BIE
                2602356BII
                2595497BII
                2595707BII
                2608597BII
                2607763BII
                2609277BII
                2613715BIE
                2613715BIE
                2604478BIE
                2604478BIE
                2614473BII
                2614313BII
                2596802BII
                2621118BII
                2621018BII
                2620792BII
                2620195BII
                2604363BII
                2620483BII
                2609144BII
                2624259BII
                2624393BII
                2619985BIE
                2619985BIE
                2615822BII
                2616099BII
                2599846BIE
                2599846BIE
                2599846BIE
                2622960BII
                2606809BIE
                2606809BIE
                2606809BIE
                2624449BII
                2622754BIE
                2622754BIE
                2620414BIE
                2620414BIE
                2625119BII
                • 5. Re: Another Calculated Field Question
                  Chrissy Scott

                  Hi Carl, I can't get your attachment to open because it was created in a new version of Tableau than the version I'm using. Is there a way to save it so that i'll be able to open it?

                  • 6. Re: Another Calculated Field Question
                    Joe Oppelt

                    I just did SUM([Ticket]) because that's what was on the sheet.

                     

                    You'll want to do a COUNTD of whatever identifies uniqueness.  So In [total tickets] you would do countd([call_number]), and likewise in [total BII tickets].  (The other one is just a subtraction ot the first two, so nothing to change there.)


                    Whether you use LOD like Carl did,  or table calcs like I did, the key is understanding how to tell Tableau how to "walk" through the table to count what you want to count (or add, or average, etc.)

                     

                    In my example I got the totals for screen by telling tableau to cycle through all the LOBs and all the Impact types.  Don't restart.  Just count it all.  And I told tableau to get the totals for each LOB by cycling through the LOBs and the Impact Types, but restart the summing with each LOB.  Now, with all those table calcs avaliable to you, you can move them around any way you want in the tooltips.  (And/or place them on the labels of the bars!)

                    • 7. Re: Another Calculated Field Question
                      Chrissy Scott

                      Thanks Joe,

                       

                      Bare with me just a bit longer if you can... I got the total tickets to work like this: { FIXED  : COUNTD([Call Number])}

                       

                      The trouble I'm having now is trying to get the BIE Total. When I use this:

                                IF [Impact Type] = 'Business Impact External' THEN COUNTD([Call Number]) END

                       

                      I get an error message that says "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions. Is just not something I can do or is the formula written incorrectly?

                       

                      Thanks again.

                      • 8. Re: Another Calculated Field Question
                        Joe Oppelt

                        Do it this way:

                         

                        countd(IF [Impact Type] = 'Business Impact External' THEN([Call Number]) END)

                         

                        Tableau doesn't want you to do an aggregation as the result of an IF statement.  Instead, aggregate the the results.

                        • 9. Re: Another Calculated Field Question
                          Chrissy Scott

                          Thanks, Joe! That got me where I needed to go. I just wrapped that all up in FIXED { } and now I'm set. I appreciate your help.

                          • 10. Re: Another Calculated Field Question
                            Joe Oppelt

                            So you are saying you FIXED{} it!

                             

                            Glad to help out.