Nested Aggregates - How Many Users Repeatedly Attended our Events?

Question & Answer
1

Hello,

I have a datasource with event attendees. Each row represents one users attendance. I’m trying to determine how many users completed more than 1 even in a calendar year. I would also like to show total completions in the same table, and % of completions that were repeated.

Event ID date Attended user
200 12/06/2022 1 AAA
201 10/06/2022 1 AAA
200 08/06/2022 1 DDD
200 08/06/2022 1 BBB
201 07/06/2022 1 BBB
200 07/06/2022 1 CCC
202 04/06/2022 1 AAA
202 02/06/2022 1 DDD
202 02/06/2022 1 EEE
202 31/05/2022 1 BBB
202 30/06/2022 1 CCC
201 30/06/2022 1 FFF
100 12/06/2021 1 AAA
101 10/06/2021 1 AAA
100 08/06/2021 1 DDD
100 08/06/2021 1 BBB
101 07/06/2021 1 BBB
100 07/06/2021 1 CCC
102 04/06/2021 1 AAA
102 02/06/2021 1 EEE
102 31/05/2021 1 BBB
102 30/06/2021 1 CCC
101 30/06/2021 1 FFF

I would like to see an output of:

Event Year Attendee w/more than 1 attendance Unique Attendees Total Attendance % of Attendance that Repeated
2021 3 6 11 50%
2022 4 6 12 67%

Any help is greatly appreciated! Thanks in advance!

2

Hi @Kashgar,

I took your dataset and using calculations and Level-aware calculations I have been able to create one possible solution as follows:

image
image840×574 25 KB

First, I truncated the date to just the year by adding a calculated field Event Year:

truncDate("YYYY",date)

Then I created a LAC-W calculated field UserCompletionsInYear to count the total number of completions per user per year:

countOver(user,[user,{Event Year}],PRE_AGG)

With these fields you can then create another calculated field MoreThanOne as follows:

ifelse(UserCompletionsInYear>1,1/countOver(user,[user,{Event Year}],PRE_AGG),0)

For attendees with more than 1 completion this calculates a ratio that can then be summed up in a visual to give you the total number of attendees (when grouping by year) who completed more than one event.

The final column Repeats, can be calculated using this calculated field:

sum(MoreThanOne)/distinct_count(user)

If you then create a table visual and drag the fields in as shown in the above image (selecting the same aggregations as shown) you should get the same result. Hopefully I’ve understood and this goes someway to helping.

Additionally, you may want to move some of the calculations into the dataset.

1 Like
3

Hi @Kashgar - did Andrew’s suggestion solve your issue? If so can you mark his response as a ‘Solution’ please?

4

Thanks so much, Andrew!

1 Like