avgOver - Displaying just one record instead of multiple records for the user

Question & Answer
,
1

Hi Team,

I have column “NUMOFAVGDAYS” and the average is showing in all the three rows for User1(TeamA).
I am looking for 1 row (instead of three rows) for User1 (Team1) which will just show 25.
What is the best way to achieve this?

Thank you!

Screenshot 2023-06-28 at 3.34.40 PM
Screenshot 2023-06-28 at 3.34.40 PM741×329 12 KB

1 Like
2

Hi @QuickSightRokie,

Can you show your calculation for the NUMOFAVGDAYS field? If you used LAC-W to calculate it, you should be able to move it before ACTIONDATE so that only one value is displayed.

1 Like
3

avgOver (DateDiffDays,[TEAM,USERID])

1 Like
4

Hi @David_Wong,
Do you need any more details?

5

Hi @QuickSightRokie,

Can you change it to this?
avgOver(DateDiffDays,[TEAM,USERID], PRE_AGG)

After you change it, I think you should be able to put it before ACTIONDATE in your pivot table and that should show you just one value for each user.

1 Like
6

Thank you so much, @David_Wong !

When I try to use the calculation, I get an error "Execution Order mismatch: PRE_FILTER calculations cant have PRE_AGG operands

7

Hi @QuickSightRokie,

Can you show your calculated field for DateDiffDays?

8

Sorry @David_Wong
Please ignore my message. I was able to display only one row for one user.

Thank you!