So, I have a use case where I need to calculate the monthly costs of my clients Quicksight usage.
If the user has an 'Author' role the cost is fixed to 18$ per month. If the user has 'Reader' access, it costs 0.30cents for 30 minutes for the first 17 sessions and 5$ fixed rate if the usage exceeds 17 sessions.(How I calculated the number of sessions is another story).
So, say I have a visual like this:
I redacted the user names for obvious reasons. I just want to add a simple calculated field called total_cost with following code:
ifelse(role = 'Author', 18,ifelse(role = 'Reader' AND {total_sessions_per_month} >= 17, 5,ifelse(role = 'Reader' AND {total_sessions_per_month} < 17, 0.30*{total_sessions_per_month}
, 0)))
I am getting a syntax error with this code. It says, "The syntax of the calculated field expression is incorrect. Correct the syntax and choose Create again."
Also, I am not entirely sure about the logic of the code here, as the field 'total_sessions_per_month' is another aggregated field.
sumOver(sum({total_sessions}),[{user_name},role,year,month])
I don't know how it fits here, as I formed a condition with an non-aggregated field(role) and aggregated field(total_sessions_per_month).
I hope someone can help me figure this out, as I am pretty new to Quicksight and BI in general.