9 Replies Latest reply on Dec 12, 2018 12:25 PM by Stephen Rizzo

    Help with Dynamic Calculations (Weighted Average) with Filters?

    Alex Barnes

      See attached workbook for context.

       

      I'm trying to create a line graph for a dashboard, which users will be able to filter. I want the user to be able to select which locations (here given as state names in the dummy data) they want, and to have it graph the weighted average compliance rates for each metric over time for those selected locations. With the dummy dataset attached, I'm looking for two lines on a single line graph (one for Metric 1, one for Metric 2), showing the overall compliance rates for each over time for the locations that a user chooses on a filter.

       

      For example, I want a user to be able to produce a line graph of just Alabama and Delaware's combined compliance rates (without California, if they so choose) for each metric. In this way, the Metric 1 Compliance rate for FY18Q1 would be 21.7% (3 Yes from Alabama and 10 Yes from Delaware means 13 Yes total, divided by 10 total from Alabama and 50 total from Delaware, or 13/60 overall). If the user de-selected Alabama on the filter, it would just show Delaware's 20% compliance rate. If they added California, they would get a value of 16.7% (15 Yes from CA, 10 Yes from DE divided by 150 total). The final product should be able to do this for any combination of states and auto-recalculate the values of the line graph, which is the part I'm struggling with.

       

      There are several additional metrics, quarters, and locations in the real data, but this data set includes each of the elements of what's required. "Unknown" counts as a noncompliant response. The percentages in the workbook have been manually calculated in Excel first, but it would be excellent if I could automate those as well (but that isn't strictly necessary, since I already have them in the workbook).

       

      Can anyone point me in the right direction to figure out how to do this, or explain how it can be done?

       

      Thank you in advance!!!

        • 1. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
          Stephen Rizzo

          The key steps required are:

           

          • Creating a calculated field that converts your fiscal quarter strings to dates (strings can't be set to "continuous", which is required to get the line chart)
          • Creating a table calculation that displays the percentage of "YES" responses for each metric

           

          See the workbook attached.

          1 of 1 people found this helpful
          • 2. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
            Alex Barnes

            Stephen,

             

            Thanks for the response! I can't seem to open your Tableau workbook though - I'm getting the following error:

             

            Unable to complete action

            Errors occurred while trying to load the workbook "C:\Users\USERNAME\Downloads\LineChart.twbx". The load was not able to complete successfully.

            Errors occurred while trying to load the workbook "C:\Users\USERNAME\Downloads\LineChart.twbx". The load was not able to complete successfully.

            Error(284,66): no declaration found for element 'simple-id' (id: C:\Users\USERNAME\AppData\Local\Temp\TableauTemp\943152468\LineChart.twb)

            Error(284,66): attribute 'uuid' is not declared for element 'simple-id' (id: C:\Users\USERNAME\AppData\Local\Temp\TableauTemp\943152468\LineChart.twb)

            Error(285,14): element 'simple-id' is not allowed for content model '((cards,viewpoint?)|(viewpoints,active,device-preview))' (id: C:\Users\USERNAME\AppData\Local\Temp\TableauTemp\943152468\LineChart.twb)

             

             

            Any idea why this might be happening?

            • 3. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
              Stephen Rizzo

              Not sure - try this version. If this doesn't work, I can just provide the calculations - it wasn't too complicated to implement.

              1 of 1 people found this helpful
              • 4. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
                Alex Barnes

                Thanks, this version worked!

                 

                Two more things to iron out though:

                 

                1) I thought I followed what you did, but I now have three separate line graphs for "No", "Unknown", and "Yes" adjacent to each other. I have "Response" across the top of my three graphs. How do I combine them all into one line graph with just the compliance rates shown (as you have)?

                 

                2) For one of my metrics, the "Yes is compliant, No is non-compliant" values actually need to be flipped, because whoever designed this survey question before I started here wasn't thinking of the implications. Can I accommodate this with some sort of conditional statement, perhaps with a calculated field or similar?

                • 5. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
                  Stephen Rizzo
                  1. Right-click on "No" and "Unknown" and select "Hide", then right-click on the [Response] pill and deselect "Show Header"
                  2. Yes, you can swap out the [Response] field for a calculated field with whatever conditional logic you want to flip the values.
                  1 of 1 people found this helpful
                  • 6. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
                    Alex Barnes

                    Thank you for all the help! Could you just explain how to write the calculated field with conditional logic switching those values only for certain Metrics? I can't seem to find a good way to do it.

                    • 7. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
                      Stephen Rizzo

                      If the number of questions to flip is small and the number of potential responses is small, you can just hard-code that logic into a calculated field. That is, instead of [Response], you would use a calculated field [Adjusted Response] defined something like (assuming Q1 is your question with flipped responses)

                       

                      IF [Metric] = "Q1" AND Response = "YES" THEN "NO"

                      ELSEIF [Metric] = "Q1" AND Response = "NO" THEN "YES"

                      ELSE [Response]

                      END

                      • 8. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
                        Alex Barnes

                        This makes sense, but it actually creates a problem with my Row calculation. I have:

                         

                        SUM([Response Value]) / TOTAL (SUM([Response Value]))

                         

                        This was working before I added the conditional logic as you described, but now I'm getting the following error:

                        The Table Calculation for _(the above)__ requires a field that is missing. Please add the missing field to the view.

                         

                        For my conditional logic, I wrote out the following (given that there are two metrics needing to be flipped):

                         

                        IF [Metric] = "ABC" AND [Response] = "YES"

                        THEN "NO"

                        ELSEIF [Metric] = "ABC" AND [Response] = "NO"

                        THEN  "YES"

                        ELSEIF [Metric] = "XYZ" AND [Response] = "YES"

                        THEN "NO"

                        ELSEIF [Metric] = "XYZ" AND [Response] = "NO" '

                        THEN "YES"

                        ELSE [Response]

                        END

                         

                        Could there be an issue with my conditional logic, or do you think it's a problem with the table calculation used for the Rows?

                        • 9. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
                          Stephen Rizzo

                          Your table calculation is likely computed on the [Response] field, which is now replaced by [Adjusted Response]. You should be able to compute the table calculation using [Adjusted Response] instead.