Indirect Set Analysis with Expression

[avastani]

Hi,

Is it possible to return a dimension value using the Set Analysis P() notation? In essence, I am looking to find the Account that had the highest sales by Product Category.

My Chart Dimension is Product Category. I have an expression that returns the highest sales contributed by an account. But I need another expression that now tells me who that account is without having to add the Account as a dimension in the chart.

Is it possible?

[qliksus]

U can do this through aggr function but if u post some sample application i will do that in set analysis

max(aggr (   max( aggr( max(sales) , productCategory)) , productCategory,account))

[avastani]

I tried the above, but that didn't work. I will try to upload a sample app.

[IAMDV · Accepted solution]

Have you considered using FirstSortedValue()? You can also use Set Expression using FirstSortedValue()

I've made two videos on using FirstSortedValue(). Please check them...

http://qlikshare.com/392

Cheers,

DV

www.QlikShare.com

[sebastiandperei]

Hi,

You can use:

=fieldValue('Account', fieldIndex('Sales',max(Sales)))

[avastani]

FieldIndex works but for simple expressions. It falls apart when we have a complex expression with multiple parts to it.

FirstSortedValue does work but with limitations. Have to get workarounds to ignore certain things.. So in summary, both are good solutions. Thanks all.

[IAMDV]

I agree with you FieldValue & FieldIndex works but if you wanted to play with Sets then you need FirstSortedValue(). Which is very powerful and you can be very clever with it.

Cheers,

DV

www.QlikShare.com