-
1. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
Stephen Rizzo Nov 28, 2018 2:19 PM (in response to Alex Barnes)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.
-
LineChart.twbx 18.1 KB
-
2. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
Alex Barnes Nov 29, 2018 6:57 AM (in response to Stephen Rizzo)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 Nov 29, 2018 7:00 AM (in response to Alex Barnes)Not sure - try this version. If this doesn't work, I can just provide the calculations - it wasn't too complicated to implement.
-
LineChart_v10.5.twbx 18.0 KB
-
-
4. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
Alex Barnes Nov 29, 2018 7:48 AM (in response to Stephen Rizzo)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 Nov 29, 2018 7:53 AM (in response to Alex Barnes)1 of 1 people found this helpful- Right-click on "No" and "Unknown" and select "Hide", then right-click on the [Response] pill and deselect "Show Header"
- Yes, you can swap out the [Response] field for a calculated field with whatever conditional logic you want to flip the values.
-
6. Re: Help with Dynamic Calculations (Weighted Average) with Filters?
Alex Barnes Dec 6, 2018 11:02 AM (in response to Stephen Rizzo)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 Dec 6, 2018 12:06 PM (in response to Alex Barnes)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 Dec 11, 2018 11:07 AM (in response to Stephen Rizzo)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 Dec 12, 2018 12:25 PM (in response to Alex Barnes)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.