KPI visual problem -- Nesting of Aggregate Functions Error or Table Calculation Attribute References Missing

Q&A
, , ,
1

Hello everyone! I have been struggling with one issue in Quicksight recently and would appreciate someone’s help!

Below, you can see the table with sample data, showing user churn across quarters.

Screenshot 2023-05-16 at 10.21.25 AM
Screenshot 2023-05-16 at 10.21.25 AM1342×234 26.8 KB

As you can see, the quarterly values of user churn are different and are stated in the “User Churn” column. My goal is to find the average of those values across quarters and to display that average in a separate KPI visual. That figure should be (18.4 + 23.0 + 13.7 + 50.3)/4 = 26.3. It’s currently stated in the adjacent column in this same table.

However, when I tried finding this average and displaying it in a separate KPI visual, I ran into many issues.

There are two main issues I am encountering:
1. Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed. This is what I get in the calculated field window itself when I try using, for example:

sum({User Churn})/dateDiff(min({ACCESS_DATE}),max({ACCESS_DATE}),‘Q’)

avg(sum({User Churn}, [{QUARTER_OF_ACCESS},{YEAR_OF_ACCESS}]))

sum({User Churn})/dateDiff(min({ACCESS_DATE}),max({ACCESS_DATE}),‘Q’)

avg(sumOver({User Churn}, [{QUARTER_OF_ACCESS},{YEAR_OF_ACCESS}], PRE_AGG))

2. Table Calculation Attribute References are missing in field wells. This is what I get in the KPI visual itself, when I try using variations of the following calculated field:

avgOver({User Churn}, [{QUARTER_OF_ACCESS},{YEAR_OF_ACCESS}])

I think I read all the articles in the forum about related issues, but all the solutions recommended there did not work for me. I tried different ways of aggregation, but no result.

Can anyone help me, please? Maybe, we can do a quick screenshare with anyone from the Quicksight team and figure this out? Thank you so much in advance!

2

Screenshot 2023-05-16 at 10.22.05 AM
Screenshot 2023-05-16 at 10.22.05 AM618×534 10.5 KB

This is how I want the resulting KPI visual to look

3

What is your churn calculated field? Or is it a calculated field?

You can do something like this if it’s not.

avg({churn},[quarters])

4

Hi Max! It’s a calculated field, found by dividing the “Lost users” column by the “total number of users” column.

avg({User Churn},[{QUARTER_OF_ACCESS}]) – I tried this but still got the same error: Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed

Any other ideas? Thank you!

5

What about avgOver({churn},[quarters])?

6

I tried that – the calculated field is accepted, but it does not display anything in the KPI visual itself. This is the error I see on the visual: Table Calculation Attribute References are missing in field wells

7

Do you have any other ideas, Max? Maybe, we could jump on a quick call some day and figure it out together?

8

Hi @kiringlebmik Wanted to circle back to let you know that Max is on vacation for the next week and a half.

@Raji_Sivasubramaniam Would you have any idea regarding this error message?

9

@Raji_Sivasubramaniam any thoughts? Thank you!

10

Are “Lost users” and “Total number of users” calculated fields?

When you use something like avgOver({churn},[quarters]), all the fields in your calculation have to be included in your visual. If they are not in your visual, that’s when you get the " Table Calculation Attribute References are missing in field wells error message.

1 Like
11

@David_Wong Yes, they are both calculated fields. What should I do in this case? It does not seem that the KPI visual accepts more than one field for references.

12

Can you show the calculation for “Lost users” and “Total number of users”?

1 Like
13

Sure!

“Total Number of users” is calculated by taking the average of EID Quarter Start and EID Quarter End from the first visual I posted: ({EID Quarter Start} + {EID Quarter End} )/2

“Lost Users” calculation is based on another column (“Ending Users Final”) and utilizes the lag function:
lag( {Ending Users Final},[{ACCESS_DATE} ASC],1).

“Ending Users Final” that I mentioned is calculated this way: distinctCount(ifelse({lost_or_not_lost *} = ‘Lost’ and {QUARTER_OF_ACCESS}={QUARTER_OF_LAST_QUERY} and {YEAR_OF_ACCESS}={YEAR_OF_LAST_QUERY},EID,null))

14

Can you try avgOver({churn, [{quarters}], PRE_AGG)?

If it doesn’t work, try changing “Ending Users Final” to use distinctCountOver instead of distinctCount and then try the above.

distinctCountOver(ifelse({lost_or_not_lost} = ‘Lost’ and {QUARTER_OF_ACCESS}={QUARTER_OF_LAST_QUERY} and {YEAR_OF_ACCESS}={YEAR_OF_LAST_QUERY},EID,null), [], PRE_AGG)

2 Likes
15

I got this error, when I tried your first recommendation without changing the “Ending Users Final”: “Execution order mismatch: PRE_FILTER calculations can’t have PRE_Agg operands.”

However, when I tried modifying the “Ending Users Final” with your code, I was not able to do it as I got this error: “The syntax of the calculated field expression is incorrect. Correct the syntax and choose Create again.”

16

Why is the error message about PRE_FILTER? Are you using PRE_FILTER anywhere? I didn’t see PRE_FILTER in any of the calculated fields that you showed me.

Are any of the following fields calculated?
QUARTER_OF_ACCESS
QUARTER_OF_LAST_QUERY
YEAR_OF_ACCESS
YEAR_OF_LAST_QUERY
EID

Sorry, I can’t tell what fields in your data are aggregated and what fields are not aggregated, so it’s hard to troubleshoot.

17

Hi @kiringlebmik

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

18

Hi David!

This totally makes sense! Here is some context about these fields:

Quarter_of_Access: extract(‘Q’,{ACCESS_DATE})
QUARTER_OF_LAST_QUERY: not calculated
YEAR_OF_ACCESS: extract(‘YYYY’,{ACCESS_DATE})
YEAR_OF_LAST_QUERY: not calculated
EID: not calculated (this is a distinct identifier of a user)

19

@David_Wong do you have any more thoughts now?

20

Hi @kiringlebmik

What is the final calculated field you are having issues with syntax?

1 Like