7 Replies Latest reply on Jun 30, 2016 11:09 PM by Christina Vercruysse

    SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA

    Christina Vercruysse

      Good evening all -

       

      I have always been an Excel user and now being asked to transform our University's Factbook (all data created in Excel (over 5000+ formulas)) into Dashboards using Tableau.  So that I am not having to re-invent the whole process of calculated fields, I'm hoping that there are Tableau users that can set my Excel formula up into an Tableau formula format.  The tables that you see below are snippets of our Enrollment Census Report.  In each cell represented with a number is a formula (as shown below the tables).  In Excel, with the 5000+ formulas that I created, I can import the data file and within milliseconds my new data is populated.  I would like to mimic this same process in Tableau which I'm finding to be wonderful if I can crack the code for a SUMIF concept but in Tableau.

       

      I do not ask for help likely until I have used EVERY source to find my answers on my own, but I am read-out, YouTubed-out, Googled-out, AND playing around-out with Tableau creating sets, groups, SUM, CONTAIN, etc... You name it, I've played tried it.  I came across a similar question on the Tableau forum but missing examples of end results.  Therefore, to help in my quest, I have provide my original Excel Factbook sample and my Tableau sample down below.

       

      MY DILEMMA:  In Tableau, how can I create the Columns (FTE)?  This is a calculated field where the FTE is based on the LEVEL (Undergraduate or Graduate) AND CR ATT (Credit Attempt Hours).  Depending on the LEVEL, IF UG and CR ATT > 0 / 12    (UG FTE)    as well as IF GR and CR ATT >0 /9   (GR FTE).  I would like for this FTE category to be appended to the END of the Tableau table show below.

       

      Is there anybody out there (Pink Floyd enthusiasts - LOL) that can help me?  Once I have this setup, I can go crazy on the remaining parts of my Tableau Factbook.

       

      Note: the blue numbers to the side correspond with the original SUMIF formulas.  The calculations written are for the Fall 2015 Column.

       

       

       

       

       

       

        • 1. Re: SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA
          Christopher Dorosky

          I'm not an expert at this, but I can help with a few pieces.

           

          Once you figure out how to calculate the values you want, change them to discrete, and drag them to the ROW shelf. This makes a new column. Don't ask me why.

           

          There are a few features you need to make friends with.

          • IIF() and how to combine it with SUM. Like SUM(IIF([SOMETHING]<2.0, 1, 0) is a way to count if "something" is less than 2.
          • Learn LOD. These allow you to do a few very cool things.
            • FIXED: Calculate exactly how I am telling you to do it.
            • INCLUDE: Take the view into account, and also account for the INCLUDED dimensions
            • EXCLUDE: Take the view into account, but do NOT account for the EXCLUDED dimensions.
          • Reference calculated fields in other calculated fields.
            • Break the problem down into the simplest chucks, solve the ones that need to be known first. This can help you get around annoying errors about aggregates of aggregates, or can't mix aggregate and non-aggregate.

           

          There are free videos under the training to watch on LOD. Also, I got some help here:

           

          How to display Both the average and the count in the same table

           

          This guy was very good and explained things better than I can.

           

          It also helps to stop trying to translate Excel to Tableau. Translate the end result, not the piece by piece process. Tableau and Excel think fundamentally differently.

           

          Good luck,

           

          Chris

          • 2. Re: SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA
            Ivan Young

            Hi Christina,

            Tableau doesn't have a SUMIF function but you can nest an if statement inside sum.  SUM(IF Condition and Condition and condition then ....).  In general you are more likely to get helped if you post some normalized data explain what your goal is and what you have tried in Tableau.

             

            Regards,
            Ivan

            • 3. Re: SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA
              Ryan Haley

              This worked for me.
              Let "Task Charge Type" equal the requirements and the "Timesheet Line Actual Work" be the thing you want to add (I'm using my own data).

              I verified that the formula worked, and you can add this calculation as a column.

              To make a calculation, just right click in the measures area and click "create calculated field". Then name it what you would like the column name to be (in my case, SUMIF), then you'll be able to add this column without using filters.

               

              SUM(IF [Task Charge Type]="Billable" THEN [Timesheet Line Actual Work] END)

               

              Here is the calculation being built.

              Sumif1.PNG

              Here is the calculation in action.

              Sumif2.PNG

               

              Here is the raw column data with the filter. The numbers match, but the disadvantage is that this filter will apply to everything on the worksheet, which we are trying to avoid. The SUMIF will allow us to filter without applying this filter to the worksheet.

              Sumif3.PNG

               

              Here is a good use case where I want to compare the planned work with ONLY the billable actual work. Of course this probably has no value, but you can see that I am able to compare two fields and only applying the "Billable" filter to one via SUMIF.

               

              Sumif4.PNG

               

              Hope this helps!

              1 of 1 people found this helpful
              • 4. Re: SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA
                Ryan Haley

                I realize that the orange text does not align in my IF/THEN, and I am cursed with the decision to take the time to fix the image, or be lazy and let it remain for the rest of my tormented days. I am certain that Randall Munroe would understand.

                • 5. Re: SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA
                  Christina Vercruysse

                  Thank you all for your responses.  The suggestions, provided formulas and visual charts helped tremendously in pointing me into the right direction.  Once researching and trying to apply what was provided, I was coming across other issues such as Error Message: “Cannot mix aggregates and non-aggregates comparisons or results in ‘IF’ expressions”.  Well, I'm elated that I was able to put this formula together (WORKS) and wanted to share for those who may have encountered the same issues that I had. 

                   

                  HOWEVER, now my Calculated Field (FTE) will generate a subtotal but not the Grand total.  What gives??? Does ANYONE have any idea on how to fix this glitch?

                   

                  CALCULATED FIELD NAMED: FTE

                  IF ATTR([LEVEL]) ="UG" THEN TOTAL(SUM([Cr Att]/12))

                  ELSEIF ATTR([LEVEL]) ="GR" THEN TOTAL(SUM([Cr Att]/9))

                  END

                  • 6. Re: SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA
                    Ryan Haley

                    Why not adhere to my example for this? I am sure the logic applies here as well.

                     

                    TOTAL(

                         SUM(

                              IF ATTR([LEVEL]) = "UG"

                                   THEN [Cr Att]/12

                              ELSEIF ATTR([LEVEL]) ="GR"

                                   THEN TOTAL(SUM([Cr Att]/9))

                              END

                         )

                    )

                     

                    Not sure why we're totaling the sum though.

                    I usually use the total feature in the menu.

                    Sumif5.PNG

                    1 of 1 people found this helpful
                    • 7. Re: SUMIF CONTAINING MULTIPLE DATA FIELD CRITERIA
                      Christina Vercruysse

                      Ryan -

                       

                      Thanks for all the personal help.  A little slow on my part, but after applying your logic, I was able to come up with the following formula that ANSWERS the request that I was looking for.  It was the SUM that I needed for [LEVEL] ="GR" and placing the TOTAL at the beginning that was throwing me off.  Trying to apply Excel Logic to Tableau is my hangup.  I will let this be my guide for future computations.  Many thanks again!!! Cris

                       

                      TOTAL(SUM(IF [LEVEL]="UG" THEN [Cr Att]/12

                      ELSEIF [LEVEL] ="GR" THEN [Cr Att]/9

                      END))

                      1 of 1 people found this helpful