Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Juhap
Contributor
Contributor

Fractile with AGGR on two dimensions but including only one dimension on table

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:

DateNumber of distinct customers per dateAverage number of distinct customers per day of the weekTop quartile for number of distinct customers per day of the week
1st Apr 202129422648x
2nd Apr 202128972622x
3rd Apr 202127762467x
4th Apr 202126762418x
5th Apr 202117551633x

 

Thanks in advance!

Labels (1)
0 Replies