Get max count for a week

[rlam1234]

I have a chart that has the following:

• A date dimension
  
  
• A measure that does the count for each day. The expression in the measure is:  Count({$<CodeDev={1} >} distinct id)

I want to get the max count for each week. 

• Dimension will be: WeekEnd(CalendarDate)
  

This will make each row the end of a week. Without changing the measure, I get this:

How do I set the expression to get the max count for a week? For example, in second chart, I want 8/29/2020 to show 3807, because the max daily count for 8/23-8/29 (in first chart) is 3807.

Any advice appreciated. 🙂

[stevedark · Accepted solution]

HI @rlam1234 

What you are wanting there is the AGGR function. Strongly suggest looking up the help file for that function, so you understand how it works, but what I think you need is:

max(aggr(Count({$<CodeDev={1} >} distinct id), Date))

This will get the counts for each date and then the max will get the highest of those, if you then have Week as a dimension it will give the highest daily count in each week.

Steve

[rlam1234]

Thanks Steve. That's what I needed to do.