Hi all,
I have data containing dates, days of week, and customerIDs, and I am trying to calculate top quartile for the number of distinct customers per day of the week. For example, as 1st of April is Thursday, I would like to get what is the top quartile for number of distinct customers within all Thursdays in my data. I could easily get this to work by adding Day of the week as another dimension, but then I cannot use this as a bar graph.
I managed to calculate the average number of distinct customers per weekday with following:
=Sum(aggr(count(distinct total <DayOfWeek> CustomerID), DayOfWeek, Date))
/
Sum(aggr(count(total <DayOfWeek> Date), DayOfWeek, Date))
Here's what my table is currently looking like:
Date | Number of distinct customers per date | Average number of distinct customers per day of the week | Top quartile for number of distinct customers per day of the week |
1st Apr 2021 | 2942 | 2648 | x |
2nd Apr 2021 | 2897 | 2622 | x |
3rd Apr 2021 | 2776 | 2467 | x |
4th Apr 2021 | 2676 | 2418 | x |
5th Apr 2021 | 1755 | 1633 | x |
Thanks in advance!