Nested Aggr

[mrainey18]

I need to find the difference between the count(x) and the max(count(x)).  Here is what I have so far:

aggr(max(aggr(count(x),CalendarDate)),CalendarYearWeek)

This works and comes out with the correct max of the week.

I set the previous expression as a Variable vMaxX.

$(vMaxX) - Count(x)

This only works if my dimensions are per day totals or complete totals for the week.  But if you try and get a day by day breakdown of the week becomes a null value for 6 out of the 7 days that week.  That one day isn't consistent or in any noticeable pattern.

When you select a specific date the max changes to that days count.  I dont want this to happen.  Each day should have an associated max for that week.

I can not do this on load because I need x to be able to change.

Added example.  The question is why the max(count(x)) is only associating with one month out of that year?

Message was edited by: Michael Rainey

[MK_QSL]

Try

Max(Aggr(Count(x),CalendarDate,CalendarYearWeek)) - Count(x)

[mrainey18]

Tried that also.  When I try and break it down by day, each day becomes its own max.  What I am trying to do would be similar to a count(x) group by GroupID, Year and Week and then map to each GroupIDYearWeek.

[sunny_talwar · Accepted solution]

How about this

aggr(NODISTINCT max(aggr(count(x),CalendarDate)),CalendarYearWeek)

or

Max(TOTAL <CalendarYearWeek> Aggr(Count(x),CalendarDate, CalendarYearWeek))

[mrainey18]

The first one works.  I might have a follow up question because there is a second part to this.  Actually I'll create a new question.

[sunny_talwar]

Sure