Getting the correct n sizes will be an issue. It looks like your survey provider only included response counts in your pre-aggregated data, whereas it looks like you may be trying to display respondent counts. That can't be reverse engineered from what you have. Also, if "Score" = mean score that will also be a problem, as 9s and 10s are merged for 0-10 response scale questions. If "Score" = top box score / percentage and %tile = percentile rank, then percentile ranks would need to be provided by your survey vendor - didn't see those in the sample data set.
In other words, I would recommend seeing if you can get a more Tableau-friendly data dump from your survey vendor if possible. Otherwise your layout may need some changes.
So the n values is the "total" part of the n column. I would need n= for all categories by adding the "totals" together which would give me the n= part of the format. Score= the "9-10" or always category. So I need tableau to pull for n= all total values for that month and the top box score which is located in the % column for only those categories. Does that make sense?
Can you explain why you choose to do the things you did? Like what was your thought process? I am struggling with figuring out when to use LOD expressions/ T & F formulas.
1 of 1 people found this helpful
Sure, I'll try. Looking at your desired output, it looks like you wanted three "levels" / dimensions for your columns - the month and year, the total number of responses for that month and year, and the names of the two outcome measures that you wanted in your table (Top Box Count and Top Box %).
The first dimension was straightforward to create, I just concatenated [Month] and [Year] into a [Month Year] field. Since "Top Box Count" and "Top Box %" correspond to the [N] and [%] measures in your data for rows associated with top box responses, [Measure Names] would work well for the third level if we filtered down to the appropriate rows in your data. For that purpose I created a Boolean calculated field ([Top Box?]) that returns TRUE whenever the row is a top box response and added that field to the Filters card. Unfortunately, this filters out all rows with the 'Total' category value, which we need for the second level / dimension. I used a FIXED level of detail expression to pull all [N] values in the 'Total' rows and sum them together for each month and year, regardless of what is in the view. Because the LOD expression is FIXED, the aggregation occurs prior to regular dimension filters being applied and allows us to turn the aggregate value into a dimension. Finally, I used that dimension in my [n= header] calculated field to add the "n = " prefix to the header.
As a general rule, you will want to consider Level of Detail expressions whenever you need to aggregate your data at a different level than what is displayed in the table / chart (in this case aggregating over all questions/sections as opposed to displaying a different n size for each question/section). INCLUDE Level of Detail expressions are helpful for calculating things like averages of averages (ex: averaging responses over multiple questions to get a section score for each respondent, then averaging over respondents to get a score for a given respondent group / provider). EXCLUDE Level of Detail expressions are helpful for calculating roll-ups. FIXED Level of Detail expressions require you to specify the exact level at which you want an aggregation calculated, but allow you to calculate your aggregation using rows that will be filtered out later on in processing, as well as allowing you to turn that aggregation into a dimension.
I hope that helps!