1> Last Activity Date:
In this case, for User1(TeamA), the Last Activity Date should display as Feb 20,2023.
2> Recency bucket:
I am also looking to see how can I place the data into buckets depending on the ACTIONDATE column.
Ex. for User1(TeamA), if the Last Activity Date for the user is Feb 20,2023, the “RECENCY” column should display “Logged in before 5 months” or “Within last 5 months”
And once we are able to get the above details, I want to also check the count of users in each bucket.
The four calculated fields are as follows: Last Activity Date maxOver(max(ActionDate), [Company,Team,UserID])
This uses the level-aware calculation to find the latest date for a user.
Recency ifelse(datediff({Last Activity Date}, now(), "MM")>4,"Over 4 months ago", "Within last 4 months")
This uses datediff to place the user into one of two date “buckets”.
Last Activity Date Count 1/countOver(ActionDate, [Company,Team,UserID])
This is a maths “trick” that we can use in the Sum of Users Per Bucket calculated field to sum-up the number of users in each bucket.
Sum of Users Per Bucket sumOver({Last Activity Date Count}, [Recency])
I’ve not fully tested this, but let me know if it provides a solution.
Hi @abacon ,
Is there a way to display only row (instead of three) for “Last Activity Date”, “Recency” , “Last Activity Date Count” and “Sum of Users per bucket”?
Trying to get average at the Master level (Team mentioned in the above example)
For this same reporting, I encountered an issue where there were same values (ex. 16.21 as shown below)
Master
User under Master (there are multiple users under Master)
Action date - is the date when the specific user had some activity
Avg Frequency [Customer] - is a calculated field.
Now, here after using the the calculated fields and having a filter on "Latest Check " calculated field, we are able to get the output, but when we add Master details, at times it shows rows with same value for “Average Frequency”. Only thing I am looking for is how to display only one row which shows Avg Frequency value of 16.21 (instead of two). A solution even without using user data (second column) will work (if we are able to display the correct output which shows the Average Frequency at the master level)
