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

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

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

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

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

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

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

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 NAME BUSINESS OWNER PROJECT MANAGER PROJECT FINISH DATE PROJECT START DATE FLIGHT PLAN CODE FINISH DATE START DATE PHASE AUCTION LOCATION DEPARTMENT IMPACT FPO00042 Training for 1st GM Sale McCollum, Toni Fraraccio, Robert 6/20/14 6/2/14 FPOD00089 6/6/14 6/2/14 LAUNCH Manheim Arena Illinois Front Office/Admin Low FPO00042 Training for 1st GM Sale McCollum, Toni Fraraccio, Robert 6/20/14 6/2/14 FPOD00089 6/6/14 6/2/14 LAUNCH Manheim Arena Illinois Inspections/Vehicle Check-In Low FPO00043 Training for 1st Chrysler Sale McCollum, Toni Fraraccio, Robert 6/13/14 6/9/14 FPOD00090 6/13/14 6/9/14 LAUNCH Manheim Minneapolis Commercial Accounts Low FPO00043 Training for 1st Chrysler Sale McCollum, Toni Fraraccio, Robert 6/13/14 6/9/14 FPOD00090 6/13/14 6/9/14 LAUNCH Manheim Minneapolis Inspections/Vehicle Check-In Low FPO00043 Training for 1st Chrysler Sale McCollum, Toni Fraraccio, Robert 6/13/14 6/9/14 FPOD00090 6/13/14 6/9/14 LAUNCH Manheim Minneapolis Body/Paint/Detail & Mechanic Shops Low FPO00043 Training for 1st Chrysler Sale McCollum, Toni Fraraccio, Robert 6/13/14 6/9/14 FPOD00090 6/13/14 6/9/14 LAUNCH Manheim Minneapolis Lot Operations Low FPO00050 VCI Dealer Blend Sale Karwoski, Chris Fraraccio, Robert 6/25/14 6/25/14 FPOD00097 6/25/14 6/25/14 LAUNCH Manheim Atlanta Sale Day Operations Low FPO00051 GM Sale - Arena Pomplun, Richard Fraraccio, Robert 6/9/14 6/9/14 FPOD00098 6/9/14 6/9/14 LAUNCH Manheim Arena Illinois Sale Day Operations Low FPO00052 GM - PA Sale Pomplun, Richard Fraraccio, Robert 6/20/14 6/16/14 FPOD00099 6/27/14 6/27/14 LAUNCH Manheim Pennsylvania Sale Day Operations Low FPO00055 Raise Your Game National Promotion Malsom, Dena 10/12/14 9/15/14 FPOD00103 10/12/14 9/15/14 ROLLOUT Manheim Seattle Promotions Low FPO00055 Raise Your Game National Promotion Malsom, Dena 10/12/14 9/15/14 FPOD00103 10/12/14 9/15/14 ROLLOUT Manheim Omaha Promotions Low FPO00055 Raise Your Game National Promotion Malsom, Dena 10/12/14 9/15/14 FPOD00103 10/12/14 9/15/14 ROLLOUT Manheim Detroit Promotions Low
• ###### 7. Re: Need to flag locations by count of impact

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

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.

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

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

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:

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

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

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:

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:

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

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

You're welcome!

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

1 2 Previous Next