I was wondering if anyone happened to know why I am getting an error when I try to use a calculated field to create a box plot. The calculated field created in QuickSight is SUM(volume) / SUM(hours). I have posted a screenshot of the error.
Hey @duncan , I don’t have any filters on the box plot chart, so I’m not sure what is causing the issue. I don’t really understand the concept of Level-Aware Calculations (LAC), could you explain this to me?
A LAC function allows you to run a calculation on your table at a specific level or partition. For example, if you didn’t want total sum of sales but sum of sales by country you could do the following:
*Important note: you have to do a partition by fields in your dataset.
I’m going to assume that those don’t exist in your analysis though. What is your datasource for this dataset?
I see, yes that is correct. In the backend query I am not doing any partitioning by fields. The datasource of this dataset is the a table that contains volume, hours, benchmark type, and sort center. I don’t believe there is any partitioning that is occurring in that data table.
It is weird that when I modify the query to do the calculated function, QuickSight is able to process it:
, SUM(a.volume) / NULLIF(SUM(a.hours),0) as TPH_adjusted
FROM table_name a
LEFT JOIN table_name2 b ON a.sc = b.sc
WHERE a.category = 'filtering_here'
GROUP BY 1, 2, 3
Is there any benefit to separating the volume and hours column, and then computing the calculated field of TPH in QuickSight vs. in the query?