1 2 Previous Next 16 Replies Latest reply on Nov 6, 2014 12:12 PM by Brad Gibson

    Need to flag locations by count of impact

    Brad Gibson

      Greetings!

       

      I have a table like this

       

      Location |  project | impact | date

       

       

      Atlanta | P1 | Medium | 1/5/2014

      Atlanta | P2 | Medium | 1/5/2014

      Ohio | P3 | Medium | 1/5/2014

      Ohio | P4 | Medium | 1/5/2014

      Ohio | P5 | Medium | 1/5/2014

      Ohio | P6 | Medium | 1/5/2014

      Ohio | P7 | Medium | 1/5/2014

       

      I am looking for a way to count occurrences of an impact for a particular time window and set a field to a value based on that result.

       

      So, in this example, if I have more than one medium impact item during the week for a location, that location should show the value of "COLLISION."

       

      for the time period 1/1/2014 - 1/6/2014 both Atlanta and Ohio should show as COLLISION because the have multiple medium impact events.

      Is there a way to show this in Tableau, preferably with a count of how many items are in the collision set?

        • 1. Re: Need to flag locations by count of impact
          Jonathan Drummey

          Hi Brad,

           

          1. Create a calculation that is IF SUM([Number of Records]) > 1 THEN "Collision" ELSE "None" END.

          2. Create a view with City and WEEK(Date) as dimensions.

          3. Bring the calculated measure from step 1 onto the Text Shelf, or Rows, or Columns, or Color. The measure will be aggregated to the granularity of City and Week.

          4. Add SUM(Number of Records) to the view as well tons how the number of records.

           

          If you need more assistance, posting a packaged workbook with some sample data and your work so far would be helpful.

           

          Jonathan

          • 2. Re: Need to flag locations by count of impact
            Brad Gibson

            thanks for the help, Jonathan.

            How does this solution differentiate between the medium, low, high?

            We allow up to 5 mediums, but only 2 high... etc.

            • 3. Re: Need to flag locations by count of impact
              Jonathan Drummey

              Do you want a separate result for each of low/medium/high, or only one result at the City/Week level?

              • 4. Re: Need to flag locations by count of impact
                Brad Gibson

                One result using conditions :

                Collision is true if:

                • more than 4 medium
                • more than one high.

                per city per week.

                • 5. Re: Need to flag locations by count of impact
                  Jonathan Drummey

                  Here's a calc that should work. In a view with city and week as dimensions, it does two separate record-level evaluations that are individually summed and evaluated at the level of city and week.

                   

                  IF SUM(IF [Impact] == "Medium" THEN 1 ELSE 0 END) > 4
                  OR SUM(IF [Impact] == "High" THEN 1 ELSE 0 END) > 1 THEN
                      ""Collision" 
                  ELSE 
                      "None" 
                  END
                  

                   

                  Cheers,

                   

                  Jonathan

                  • 6. Re: Need to flag locations by count of impact
                    Lori McKee

                    Jonathan Drummey Thanks for the help!! Brad and I work together and I tried your solution.... it did not work (VERY CLOSE), but I wanted to provide you with our actual data and criteria we are working with... it's several rows of data with multiple lines, but the "Project Id" is the unique identifier (see below for data example).  Also, the criteria we are trying to work out is is this (please bare with me... I am not technical):

                     

                    IF COUNTD(PROJECT ID) > 5 then "Collision"

                    OR if there is more than 1 "high" IMPACT then "Collision"

                    OR if there is 1 "high" IMPACT AND more than 1 "medium" IMPACT then "collision"

                    OR if there are more than 2 "medium" IMPACT then "collision"

                    else

                    "No Impact"

                     

                    EXAMPLE DATA:

                    PROJECT ID

                    PROJECT NAMEBUSINESS OWNERPROJECT MANAGERPROJECT FINISH DATEPROJECT START DATEFLIGHT PLAN CODEFINISH DATESTART DATEPHASEAUCTION LOCATIONDEPARTMENTIMPACT
                    FPO00042Training for 1st GM SaleMcCollum, ToniFraraccio, Robert6/20/146/2/14FPOD000896/6/146/2/14LAUNCHManheim Arena IllinoisFront Office/AdminLow
                    FPO00042Training for 1st GM SaleMcCollum, ToniFraraccio, Robert6/20/146/2/14FPOD000896/6/146/2/14LAUNCHManheim Arena IllinoisInspections/Vehicle Check-InLow
                    FPO00043Training for 1st Chrysler SaleMcCollum, ToniFraraccio, Robert6/13/146/9/14FPOD000906/13/146/9/14LAUNCHManheim MinneapolisCommercial AccountsLow
                    FPO00043Training for 1st Chrysler SaleMcCollum, ToniFraraccio, Robert6/13/146/9/14FPOD000906/13/146/9/14LAUNCHManheim MinneapolisInspections/Vehicle Check-InLow
                    FPO00043Training for 1st Chrysler SaleMcCollum, ToniFraraccio, Robert6/13/146/9/14FPOD000906/13/146/9/14LAUNCHManheim MinneapolisBody/Paint/Detail & Mechanic ShopsLow
                    FPO00043Training for 1st Chrysler SaleMcCollum, ToniFraraccio, Robert6/13/146/9/14FPOD000906/13/146/9/14LAUNCHManheim MinneapolisLot OperationsLow
                    FPO00050VCI Dealer Blend SaleKarwoski, ChrisFraraccio, Robert6/25/146/25/14FPOD000976/25/146/25/14LAUNCHManheim AtlantaSale Day OperationsLow
                    FPO00051GM Sale - ArenaPomplun, RichardFraraccio, Robert6/9/146/9/14FPOD000986/9/146/9/14LAUNCHManheim Arena IllinoisSale Day OperationsLow
                    FPO00052GM - PA SalePomplun, RichardFraraccio, Robert6/20/146/16/14FPOD000996/27/146/27/14LAUNCHManheim PennsylvaniaSale Day OperationsLow
                    FPO00055Raise Your Game National PromotionMalsom, Dena 10/12/149/15/14FPOD0010310/12/149/15/14ROLLOUTManheim SeattlePromotionsLow
                    FPO00055Raise Your Game National PromotionMalsom, Dena 10/12/149/15/14FPOD0010310/12/149/15/14ROLLOUTManheim OmahaPromotionsLow
                    FPO00055Raise Your Game National PromotionMalsom, Dena 10/12/149/15/14FPOD0010310/12/149/15/14ROLLOUTManheim DetroitPromotionsLow
                    • 7. Re: Need to flag locations by count of impact
                      Jonathan Drummey

                      Hi Lori,

                       

                      Thanks for posting some data, however a text or Excel file of the data would be better, and even better than that would be a packaged workbook with some sample data and your work so far.

                       

                      There are two reasons why.

                       

                      1) The project id is not a unique identifier of record, because multiple records have the same value. Therefore it's uniquely identifying something else.

                       

                      2) You haven't specified the dimensions in the view that the COUNTD, etc. are being aggregated over. The original post talked about city and date, this data has a city but multiple date fields and assorted other dimensions. Without being clear about what's in the view for dimensions, I can't help you identify what's going wrong with your calc.

                       

                      Jonathan

                      • 8. Re: Need to flag locations by count of impact
                        Shawn Wallwork

                        All questioners please take note:

                        Jonathan Drummey wrote: Thanks for posting some data, however a text or Excel file of the data would be better, and even better than that would be a packaged workbook with some sample data and your work so far.

                         

                        This is such great advice it's worth repeating many time! (And we have.) Please, please, help us better help you by heeding this advice! Bonus points if you begin your questions with:

                         

                        Please find my 8.x Workbook attached.

                         

                        Right at the top, so it is obvious you've taken the time to design a good question. Thank you,

                         

                        --Shawn

                        • 9. Re: Need to flag locations by count of impact
                          Lori McKee

                          Thanks for the helpful advice (I am a newbie at this). Attached are my data file and workbook.  Thanks again for your reply. Here is the criteria again:

                           

                          IF COUNTD(PROJECT ID) > 5 then "Collision"

                          OR if there is more than 1 "high" IMPACT then "Collision"

                          OR if there is 1 "high" IMPACT AND more than 1 "medium" IMPACT then "collision"

                          OR if there are more than 2 "medium" IMPACT then "collision"

                          else

                          "No Collision"

                          • 10. Re: Need to flag locations by count of impact
                            Jonathan Drummey

                            You hadn't specified what the problem was in your posts here, in the workbook there's a statement that "The results are inconsistent due to the multiple records with the same Project ID and Impact." Does that mean that you only want to count each combination of Project ID and Impact once within each Auction Location, no matter how many underlying records there might be?

                             

                            Here's a visual to help:

                             

                            2014-11-06 08_41_45-Tableau - Sample of Collision Criteria.png

                             

                            For "All US Auctions", should that count as 1 Low and 2 Mediums (based on the Project ID & Impact criteria I mentioned above), or 1 Low and 3 Mediums (based on SUM(Number of Records))?

                             

                            For Manheim Albany, using the criteria I'd mentioned would be 1 Low, 2 Medium, and 1 High, vs. 1 Low, 4 Medium, and 2 High.

                             

                            Either way this can be calculated, the key is to understand the logic required vis-a-vis the dimensions in the view.

                             

                            Jonathan

                            • 11. Re: Need to flag locations by count of impact
                              Lori McKee

                              Thanks for your response. "Does that mean that you only want to count each combination of Project ID and Impact once within each Auction Location, no matter how many underlying records there might be?" Yes, That is exactly what I am trying to achieve. Yes, for Manheim Albany, using the criteria you mentioned would be 1 Low, 2 Medium, and 1 High and NOT 1 Low, 4 Medium, and 2 High.  Thanks for your help.

                              • 12. Re: Need to flag locations by count of impact
                                Jonathan Drummey

                                See the attached, here's what I did:

                                 

                                1) Considered the granularity of the data (what makes for a unique row in the data) vis-a-vis the granularity of the view (the dimensions in the view). In this case the granularity of the data is at something finer than Auction Location, Project ID, and Impact. The granularity of the view is at Auction Location.

                                 

                                2) The goal is to have a flag calc that is based on the distinct number of project/impact combinations for each Auction Location.

                                 

                                3) Given #1 and #2, that means that we have to some form of aggregation. There are three levels of calculation in Tableau: record level,aggregate, and table calculation. Since the view has the granularity of Auction Location, it's possible to use a combination of record level calcs and regular aggregates to get the desired results.

                                 

                                4) Duplicated your original worksheet to create a crosstab. I find these useful when working out calcs.

                                 

                                4a) Changed your # of Projects calc to be COUNTD([PROJECT ID]).

                                 

                                5) Changed your Sum of High calc to # of High using the following formula:  COUNTD(IF [IMPACT] == "High" THEN [PROJECT ID] END). The inner IF statement is a record-level calc that returns the Project ID only for High Impact, otherwise Null. With the granularity of the view at Auction Location, the outer COUNTD then only counts the High Impact Projects. Put that calc into the view.

                                 

                                6) Did the same for the Sum of Medium calc. Put that calc into the view.

                                 

                                7) Created a new "Calc jtd test" that implemented your criteria in a step-wise fashion with the following formula:

                                 

                                IF [# of Projects] > 5 THEN
                                    "Collision project"
                                ELSEIF [# of High] > 1 THEN
                                    "Collision high"
                                ELSEIF [# of High] == 1 AND [# of Medium] > 1 THEN
                                    "Collision both"
                                ELSEIF [# of Medium] > 2 THEN
                                    "Collision medium"
                                ELSE
                                    "No collision"
                                END
                                

                                 

                                8) Brought that calc into the view on Rows, to the left of Auction Location. This now groups the Auction Locations by the result so I can visually validate the results are accurate:

                                 

                                2014-11-06 14_01_57-Tableau - Sample of Collision Criteria.png

                                 

                                 

                                9) Created an optimized version of the calc with the following criteria:

                                 

                                IF [# of Projects] > 5 
                                  OR [# of High] > 1 
                                  OR ([# of High] == 1 AND [# of Medium] > 1)
                                  OR [# of Medium] > 2 THEN
                                    "Collision"
                                ELSE
                                    "No collision"
                                END
                                

                                 

                                10) Brought that into the view to the left of the test calc to validate:

                                 

                                2014-11-06 14_03_19-Tableau - Sample of Collision Criteria.png

                                 

                                11) Duplicated the worksheet and rearranged pills to make the view look like your original Collision Report view.

                                 

                                Workbook is attached. If you have any questions, let me know!

                                 

                                Jonathan

                                • 13. Re: Need to flag locations by count of impact
                                  Lori McKee

                                  Jonathan Drummey Thank you so much!! This is right on the money. I now understand why it is so important to provide all of the data and worksheet up front. I appreciate at you sticking with this question. Thanks again!

                                  • 14. Re: Need to flag locations by count of impact
                                    Jonathan Drummey

                                    You're welcome!

                                     

                                    On Thu, Nov 6, 2014 at 2:33 PM, Lori McKee <

                                    1 2 Previous Next