Get proper label (dimension) in addition to Aggr(max())

[danieloberbilli]

Hi,

I don't have any sample data to this issue - its rather a conceptual question.

I have a result table with the Dimension Category.

As expression I have the following Aggr() function which works fine:

max(aggr(count(distinct ID) / count(distinct total <Date,Category> ID, Department, Category))

As you see, this functions looks up the max % value per Category (and Date) based on the Department.

Now what I would like to get/extract is the proper Department name which actually causes the max-Value.

I tried several times something like If(Aggr()... then Only(Department)...but couldn't get any result. I wonder if the solution is rather a modified Aggr() function, Set Analysis or an If-function.

Any ideas?

Thanks a lot.

Kind Regards

Daniel

[sushil353 · Accepted solution]

Hi,

Try to use Firstsortedvalue function:

Firstsortedvalue(Department,yourAggrExpression)

HTH

Sushil

[danieloberbilli]

Thanks Sushil!

This works when I also add an additional aggr( ) around the original max(aggr()) function.

One last question: it works fine except one row in my result table where I get a null as result. I guess this is because there is more than 1 Department found (according to the manual the firstsortedvalue() returns null). Do you have any experience how to solve this problem when dealing with multiple values instead of one Max?

Thanks in advance

Daniel

[danieloberbilli]

I just realized this one null results because of something different.

Thanks a lot!

[sushil353]

Welcome