Help in Set Analysis

[#1]

Hi Community,

I am facing a problem in using MAX function in set analysis:

My expression is:

=MAX(SUM({<MM_CATEGORY = {'21076'},MM_UPDATEDATE={">=$(=Date(Num(yearStart(Max(MM_UPDATEDATE))),'DD-MM-YYYY'))<=$(=Max(MM_UPDATEDATE)))"}>}MM_PRINCIPAL*EXCHANGE_RATE))

I want the maximum value of this complete expression. As you can see I have used MAX func. in the beginning but it does not work. I have a taken Month in my dimension.

My objective is to show the Maximum outstanding balance in a month.

Regards,

Usama

[sunny_talwar · Accepted solution]

You need a Aggr() function here:

=MAX(Aggr(

SUM({<MM_CATEGORY = {'21076'},MM_UPDATEDATE={">=$(=Date(Num(yearStart(Max(MM_UPDATEDATE))),'DD-MM-YYYY'))<=$(=Max(MM_UPDATEDATE)))"}>}MM_PRINCIPAL*EXCHANGE_RATE), Month))

or this:

=MAX(TOTAL Aggr(

SUM({<MM_CATEGORY = {'21076'},MM_UPDATEDATE={">=$(=Date(Num(yearStart(Max(MM_UPDATEDATE))),'DD-MM-YYYY'))<=$(=Max(MM_UPDATEDATE)))"}>}MM_PRINCIPAL*EXCHANGE_RATE), Month))

[#3]

The result is the same. It shows the total sum of a whole month. I want to show the Maximum value in a month.

[#4]

Works fine on changing the correct dimension. Thank you very much.

[Kushal_Chawda]

Remove month from dimension as create the Expression for that

Month

FirstSortedValue(distinct MONTH, -aggr(SUM({<MM_CATEGORY = {'21076'},MM_UPDATEDATE={">=$(=Date(Num(yearStart(Max(MM_UPDATEDATE))),'DD-MM-YYYY'))<=$(=Max(MM_UPDATEDATE)))"}>}MM_PRINCIPAL*EXCHANGE_RATE),MONTH))

Value

FirstSortedValue({<MM_CATEGORY = {'21076'},MM_UPDATEDATE={">=$(=Date(Num(yearStart(Max(MM_UPDATEDATE))),'DD-MM-YYYY'))<=$(=Max(MM_UPDATEDATE)))"}>}MM_PRINCIPAL*EXCHANGE_RATE, -aggr(SUM({<MM_CATEGORY = {'21076'},MM_UPDATEDATE={">=$(=Date(Num(yearStart(Max(MM_UPDATEDATE))),'DD-MM-YYYY'))<=$(=Max(MM_UPDATEDATE)))"}>}MM_PRINCIPAL*EXCHANGE_RATE),MONTH))