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. 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.
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.
Do you want a separate result for each of low/medium/high, or only one result at the City/Week level?
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 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"
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.
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 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.
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?
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.
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.
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.
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 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!