2 Replies Latest reply on Oct 10, 2016 11:40 AM by Patrick A Van Der Hyde

    Calculating peer group averages based on multiple parameters

    joyce.chapman

      Hello everyone!

       

      I'm still new to Tableau. I'm trying to use Tableau to reproduce a data dashboard that I created in PHP a couple years ago. I'm having trouble figuring out how to reproduce the data columns, which display data for 1. A selected library in a selected year, 2. An average for all libraries in the selected comparison group that match the group of the selected library, and 3. The difference between the data for the selected library and the comparison group’s average. I have attached my Tableau workbook. Thank you in advance for any assistance!

       

      ISSUES

      1. “Data” column: When a library and a year are selected in the drop downs, I thought that dragging the measure “Data” to the Text shelf would display the value for that library in that year. It is displaying something completely different (I’m not sure what). The example I’ve left selected in the worksheet is FY2013-2014, Altamonte Springs City Library’s circulation of children’s materials: it should be 18,233 but shows 163,020. I can see that changing the values of the year filter on the right does not affect the data -- so the data is actually not actually being filtered.
      2. “Data-peerAverage”: The idea here is to take the peer comparison group selected in the parameter, identify which group that is for the library that is selected, and then calculate an average value for all libraries’ data for that group in the selected year. For example, if Altamonte Springs City Library is selected along with the comparison group "Revenue per capita," the average would be for all libraries falling in the same group as this library, "$0-$10". I tried creating two parameters that would hold the user’s selected values for the year and the comparison group, then storing the selected value in two calculated variables (ParameterLink-comparisonGroup and ParameterLink-FY). You can see both these calculated fields currently displayed in the Tableau table for testing purposes, and they are updating correctly when the parameter selection changes. But it’s not doing what I hoped -- I can change the comparison group or year with the selector drop downs and the Data-peerAverage doesn’t change. The formula I wrote to try and achieve the average peer group value was: "AVG(IF [FY]=[ParameterLink-FY] AND [Population Group]=[ParameterLink-comparisonGroup] OR [Revenue Per Capita Group]=[ParameterLink-comparisonGroup] OR [Library Type]=[ParameterLink-comparisonGroup] THEN [Data] END)". My best guess after reading a lot of forum posts
      3. Difference between #1 and #2 above as a percent. This seems like it would be the easiest part, but I tried creating a calculated field with the formula “([Data]-[Data-peerAverage])/[Data-peerAverage]” and got the error “Cannot mix aggregate and non-aggregate arguments with this function.” I looked it up but can't quite understand how it applies.

       

      Sorry to have three questions in one post. I suspect they may all be related to some core issue around the parameters though.

       

      Joyce