Max Value by Date and Member

[nelson_sm]

I'm having a problem with getting an expected total in a table for the following expression.

max(aggr(sum(aggr(max(Days),MemberId, Month)),Month))

Excel data set is attached.

Below is a tabular view of what we are expecting.

Total should be (88+4+52+52+6+26+18) = 246

MemberId	Jun	July	Aug	Sep as of 9/15
1	11 days	42 days	73 days	88 days
3		4
2		6	37	52
4		6	37	52
7			6
5			11	26
6			3	18
Values	11	(42+4+6+6) = 58	(73+37+37+6+11+3) = 167	(88+52+52+26+18)/5 = 236

Any ideas how to accomplish this?

Thanks in advance.

[Vegar]

As you don't have any values for member 3 and 6 in September you will need to create a expression that looks outside each table cell member and month boundary. 

Maybe something like this. 

Aggr( Rangemax( Above( Max(Days),0,rowno())),MemberId,Month)

Another alternative to avoid the use of rangemax above is to calculate the values for missing months/dates in the script. 

[nelson_sm]

Thanks Vegar.

Unfortunately, I'm still not getting the total of 246.

[Kushal_Chawda]

@nelson_sm  I don't think so it is possible to get the customized total with max value and keeping the base value as sum. Because total always evaluated based on base expression. Like currently expression which you are using giving you max value evaluated for each Month as final aggregation is Max. 

But you can probably tweak it to get the desired results. see the attached

[nelson_sm]

Hi Kush,

Your tweak was a great idea although when I modified the calculation to as follows it will not pick up the expression for Dim 2.  Any idea why?

Pick(Dim,
sum(aggr(Max(Days),MemberId)),
Max(aggr(sum(aggr(Max(Days),MemberId,Month)),Month))
)

Thank for your feedback.

[sunny_talwar · Accepted solution]

@nelson_sm to do this, add Dim to your Aggr() functions

Pick(Dim,
sum(aggr(Max(Days),MemberId, Dim)),
Max(aggr(sum(aggr(Max(Days),MemberId,Month, Dim)),Month, Dim))
)

But you can also try this without using Dim

If(Dimensionality() = 0,
Sum(Aggr(Max(Days), MemberId)),
Max(Aggr(Sum(Aggr(Max(Days), MemberId, Month)), Month))
)

Used the sample provided by @Kushal_Chawda (thanks)

[nelson_sm]

Thanks so much Sunny!  Worked like a charm.