Table calculation attribute references are missing in field wells error while working with Lead Function

Question & Answer
, , ,
1

Hi Team,

Couple of days ago, I posted about a query about calculating the difference between two dates of the same column and with making the use of Lag function. Then I stumbled upon the Lead function instead which helped me in the calculation I was looking for. This is the link of my open query:

Calculate Difference between the dates from the same column - Question & Answer - Amazon QuickSight Community

Now, since I can have another date column with the help of Lead function, I can correctly calculate the Day Differences. But the next issue I’m having is to put this Day difference field in further Aggregations.

As mentioned in my previous Question, I need to check for how many days my customer spend on each status. I need to have a cumulative sum of all the days spent on each status. For example: CustA spends 8 days on ‘On-Hold’, 0 days on 'Active. CustB spends 3 days on On-Hold and 10 days on Active.

I need to show 2 things on my Chart:

  1. I should be able to drag the status on X axis, and put summation of days spend on Value to show as Y-axis. That is: On-Hold: 11 days and Active: 10 days

  2. I need to show Average time on each status: As per above example, On Hold Average = 8+3/2 and Active Average: 0+10/2

I have tried number of functions to calculate the sum of Day-Difference with using ‘sum’, ‘sumOver’, ‘runningSum’ but each time either it gives Mismatched aggregation issue or if not then I get ‘Table calculation attribute references are missing in field wells’ error.

Below are some of the logics I’m trying to apply but all giving me same error:
sumOver( {Days Difference}, [{CustomerStatus}] )
avgOver( {Days Difference}, [{CustomerStatus}] )
sumOver( {Days Difference}, [{CustomerStatus}, {Customer_ID}] )

other calculations used are:
Days Difference: dateDiff(min(status_date),{Lead Date},‘DD’)
Lead Date: lead(min({status_date}), [{status_date} ASC],1,[{completed_customers}])

Can this please be helped? Really need to solve it somehow.

Many Thanks!

Regards,

2

hi @HimaniS,

can you please try with adding PRE_FILTER into your sumOver() or AvgOver()?

sumOver( {Days Difference}, [{CustomerStatus}],PRE_FILTER )
avgOver( {Days Difference}, [{CustomerStatus}] ,PRE_FILTER)
sumOver( {Days Difference}, [{CustomerStatus}, {Customer_ID}],PRE_FILTER )

kind regards,
Wakana

1 Like
3

Hello @HimaniS, did the suggestion made by @Wakana help resolve the issue you are facing? If not, let us know what other errors you are receiving and we can try to guide you further. Otherwise, please mark her response as the solution. Thank you!

4

Hello @HimaniS, since we have not heard back from you with more information to guide you further, I will archive this topic. If you still need assistance with this question, please post a new topic in the community and link this question. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!