I have a calculated field ‘Recommendation Type’ that I want to convert to dimension, so that I can use it as a column in aa pivot table. but I got error say: Custom aggregation field not allowed as a dimension. Below is the formula:
‘Recommendation Type’
ifelse({running total no. of headcount_t1}<${NoCSM},‘Maintain or Add’,
{running total no. of headcount_t1}=${NoCSM},‘recommended_allocation_model’
,{running total no. of headcount_t1}>${NoCSM},‘Remove or Keep No Coverage’,null)
running total no. of headcount_t1
runningSum
(
max({csm_time_spent_assumption})
,[${SuccessMetric} DESC ]
)/100
Hello @Cindy, basically, QuickSight doesn’t like that you are using an aggregation like runningSum to determine a group by column value in a pivot table. The pivot tables in QuickSight, really require simple values to function as expected. If you want to make this work with the group by values that you are building within that ifelse statement, you would want to do this within a custom SQL statement on your dataset. You can build that running sum in custom SQL and add your Recommendation_Type field. Using where clauses you could determine which runningSum values would be returned for each group and then use UNION to join each respective group of values.
If building this in custom SQL is not possible for your use case, then I think you will want to find an alternate visual to display this information.
Hello @Cindy, did my previous response help guide you to your expected output? I’ll mark it as the solution, but if you have further questions, please let me know!