topic Help in Set Analysis in QlikView

Help in Set Analysis

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help in Set Analysis

sunny_talwar — Fri, 20 May 2016 07:03:56 GMT

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))

Re: Help in Set Analysis

Fri, 20 May 2016 07:10:04 GMT

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

Re: Help in Set Analysis

Fri, 20 May 2016 07:15:01 GMT

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

Re: Help in Set Analysis

Kushal_Chawda — Fri, 20 May 2016 07:19:01 GMT

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))