Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@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)
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.
Thanks Vegar.
Unfortunately, I'm still not getting the total of 246.
@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
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.
@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)
Thanks so much Sunny! Worked like a charm.