3 Replies Latest reply on Apr 6, 2018 9:55 AM by Eric Hammond

    Grouping Counts

    Daniel Jung

      Hi everyone,

       

      I'm really stuck on a problem with aggregating data.

      So what I have is a bunch of facilities that have done a number of assessments at a given time. So what I've done so far is count all the assessments done in a particular facility up to a certain date or period (past day, week, month, etc). Then what I do is get the average number of assessments for n periods back before that date.

       

      For example, lets say I have something like this:

      Period = week

      N Periods = 4

      # of total assessments past week = 15

      # of total assessments 1 week before the first week (so # of assessments from 2 weeks ago to 1 week ago) = 16

      # of total assessments 2 weeks before first week = 14

      # of total assessments 3 weeks before first week = 10

      # of total assessments 4 weeks before first week = 12

      Avg assessments of N (or 4) periods would be (16+14+10+12)/4 = 13

       

      Then what I do is calculate the difference between the recent # of assessments to the average historical # of assessments

       

      So I have 15 - 13 = 2

       

      I do this calculation for several facilities. So I could have numbers like 3, 15, -2, 3, -12, etc.

       

      What I do is I categorize each facility with either "Good" or "Bad" depending on if they have  a positive value for the delta.

       

      So this is where my problem begins. I want to create a pie chart which counts the number of "Good" facilities and "Bad" facilities.

       

      But there is also something else, every facility belongs to an organization, and those organizations belong to partners.

      Example:

       

      Partner
      OrganizationFacilityAsesssmentIDDate
      Partner1Org1Fac11...
      Partner1Org1Fac12...
      Partner1Org1Fac23...
      Partner1Org2Fac34...
      Partner2Org3Fac45...
      Partner2Org3Fac46...

                           

       

      So I want to also be able to to find the good and bad for those categories. (Is the aggregation of all assessments for a particular facility Good? Is the aggregation of all assessments for a particular Organization or Partner Good?, etc).

       

      What I have is something like this:

       

      Recent Assessment Count =

      COUNTD(IIF(

      DATEADD([Period], -1, [Today Fake]) < [Assessment Created At]

      AND

      [Assessment Created At] <= [Today Fake],

      [Assessment Id], NULL))

       

      [Period] = Period timeframe (day, week, month, quarter, year)

      [Today Fake] = Temporary variable to select todays date (I'm looking at some old data)

      [Assessment Created At] = The day the assessment was done

      [Assessment Id] = The Unique identifier for the assessment

       

      So Recent Assessment Count counts the total assessment done in the past day, week, month, etc.

       

      Then what I have is:

       

      Historical Assessment Average Count =

      COUNTD(IIF(DATEADD(

      [Period], -[Number of Periods] - 1, [Today Fake]) <= [Assessment Created At]

      AND

      [Assessment Created At] <= DATEADD([Period], -1, [Today Fake]),

      [Assessment Id], NULL))/[Number of Periods]

       

      [Number of Periods] = The number of periods to look back from the recent period

       

      So Historical Assessment Average Count calculates the average assessments done over N periods.

       

      Then what I do is calculate the Delta Assessment Count =

      [Recent Assessment Count] - [Historical Assessment Average Count]

       

      which will yield positive and negative numbers for every facility, organization, or partner, which then categorizes them into either "Good" or "Bad"

       

      So then i create something called Assessment KPI =

      IIF([Delta Assessment Count] >= 0, "Good", "Bad")

       

      And at this point honestly I thought I could do something like

      COUNT(IIF([Delta Assessment Count] >= 0, "Good", "Bad")) and then select Facility, Organization, or Partner as the dimension, but I get an error saying:

      Argument to Count (an aggregate function) is already an aggregation and cannot be further aggregated

       

      What I would like to do is create a pie chart that counts all the facilities/orgs/partners who are good/bad.

       

      I'm fairly new to tableau so maybe there's something I'm misunderstanding but this is my current issue. If there's any more information required, I will try to provide it.

       

      Any help would be greatly appreciated!

        • 1. Re: Grouping Counts
          Eric Hammond

          Hi Daniel,

           

          You have done the hard part; that last calc was just one too many.  For the Pie chart:

          • Change the Mark to Pie
          • Put COUNTD([Facility]) on the Angle tile.
          • Select [Assessment KPI] from the measures section of the data pane; press CTRL and select [Facility] from the dimensions section of the data pane; then drag these two fields together to the color tile.
          • In the Marks shelf, click-and-drag to move AGG(Assessment KPI) into the first position.  "Good" should now be one color, "bad" the other, but with different shades within each differentiate between the facilities.
          • Use the size tile to make larger.
          • Right-click on the Facility pill in the Marks shelf, sort, field, Assessment KPI (this groups the good together and the bad together for a better display).

          • 2. Re: Grouping Counts
            Daniel Jung

            Hi Eric,

             

            Thank you so much for getting back to me on this issue. This is what I was looking for. I just had to make a small adjustment and use COUNTD instead of COUNT for the facility names because I have multiple rows that contain other information as well.

             

            While I was waiting for a reply I came up with another solution but I think it is less elegant.

             

            What I ended up doing is creating two new measures called "Good Facilities" and "Bad Facilities"

            calculated as:

             

            [Good Facilities] = IIF([Delta Assessment Count] >= 0, 1, NULL)

            [Bad Facilities] = IIF([Delta Assessment Count] < 0, 1, NULL)

             

            and arranged the pie as such:

             

            I think the main difference between this and your solution is that mine gives equal weight if I change the level of dimension to Organizations, whereas your pie will give a larger portion to organizations who contribute more facilities to the overall area.

             

            As a follow up question I wanted to make sure I understand how the data is being aggregated depending on that change of dimension and how it would change the display.

             

            Let's say I have the following data defined:

             

            4 Total Orgs, 20 total facilities

            Org1 has 9 bad facilities and 2 good facilities

            Org2 has 1 bad facility and 2 good facilities

            Org3 has 1 bad facility and 2 good facilities

            Org4 has 1 bad facility and 2 good facilities

             

            Let's say I have the [Assessment KPI] already in the color tile in the mark shelf and the COUNTD([Facility Name]) already in the angle tile.

             

            If I moved [Facility Name] to the color tile in the mark shelf then I would get a pie chart that consists of:

            - 8 slices in the pie considered Good 40%

            - 12 slices in the pie considered Bad 60%

            - All slices are equal size

             

            However, if I moved [Organization Name] to the color tile instead then I would get a pie chart that consists of:

            - 3 slices in the pie considered Good 75%

            - 1 slice in the pie considered Bad 25%

            - All slices are equal size

             

            Now the above assumes that every organization is treated equally and I think that's what I've done with my solution, so it gives an inaccurate representation of the underlying data (when displaying as organizations).

             

            If I went along with your solution then the results would be like below:

             

            [Facility Name] in color:

            - 8 slices in the pie considered Good 40%

            - 12 slices in the pie considered Bad 60%

            - All slices are equal size

             

            [Organization Name] in color:

            - 1 slice in the pie considered Bad with an area worth 11 slices (55% Bad overall)

            - 3 slices in the pie considered Good each with an area worth 3 slices. 9 slices total (45% Good overall)

             

            Although not a perfect representation of the data at the facility level, it is much better than my solution.

             

            Is my understanding of all this correct?

            • 3. Re: Grouping Counts
              Eric Hammond

              Hi Daniel,

               

              The way I understand the data that you have presented, Org1 (with 9 bad facilities and 2 good facilities) isn't necessarily a bad Org.  If each of the 9 bad facilities had a delta of -1, and each of the 2 good facilities had a delta of +5, then the net delta of Org1 would be +1 (good).  To count facilities instead of assessments then some of the calculated fields would need to be revised.

               

              Next, the way I would answer your questions about expected behavior is to just make the changes to the worksheet and verify the results.  However, I didn't save the worksheet that I used to prepare my initial response, and it was a pretty rough representation anyway.  I recommend that you make a copy of the worksheet and then play around with the different options until you are comfortable with the results.  One suggestion: even when using organization on color, you can still put facility on the detail tile to create more pie slices, which may help to arrive at the desired presentation.

              1 of 1 people found this helpful