Hi @Kavtya , Thanks for reaching out. I believe you are trying to do nesting of aggregate functions (one aggregate function inside another). You might be hitting an exception: Custom aggregations can’t contain nested aggregate functions.
To solve this issue you need to break complex calculations into separate calculated fields example, Create a calculated field first for the date part then create another calculated field to sum the amount.
Alternatively, If you need to compare quarters or do time-based aggregations, consider using QuickSight’s built-in date functions and time grain controls instead of nested calculations.
- Changing date field granularity
- Add comparative and cumulative date/time calculations in Amazon QuickSight
Hope this helps!
"Hi @harpson,
I have a situation where I need to perform a calculation similar to this:
sumIf({portfolio_outstanding}/10000000, truncDate('DD', {new_date}) = parseDate('2024-12-31', 'yyyy-MM-dd'))
The issue is that instead of using {portfolio_outstanding}/10000000, I need to use a value derived from another calculation. Let’s call this calculation concept ‘Disb’:
Disb = (sum({sanctioned_amount}) - {2 Prev_Quarter_santion}) / 10000000
Essentially, I want to calculate the sum of these ‘Disb’ values, but only for rows where the new_date matches a specific date (like 2024-12-31).