14 Replies Latest reply: Nov 12, 2015 6:15 AM by Sunny Talwar

# FirstSortedValue with  set analysis

hi all,

I have a straight table (best reps) with 1 dimension and 2 expressions

dimension is department, and the expressions are:

1. Rep. Name

FirstSortedValue(REP_NAME, -Aggr(Sum(AMOUNT),DEPARTMENT, REP_NAME))

2. SALES AMOUNT

Max ( aggr ( sum ( AMOUNT ), DEPARTMENT, REP_NAME ) )

I want to add an expression that will sum reps (from expression 1) the quantity  sold,

by default when I add sum(quantity ) it calculates the dimensions quantity.

any ideas?

• ###### Re: Best Rep. table

May be try this:

Sum({<REP_NAME = {"=FirstSortedValue(REP_NAME, -Aggr(Sum(AMOUNT), DEPARTMENT, REP_NAME))"}>}Quantity)

• ###### Re: Best Rep. table

did not work

: (

• ###### Re: Best Rep. table

Like below!!

Max (aggr (sum ( Quantity ), DEPARTMENT, REP_NAME ) )

• ###### Re: Best Rep. table

hi,

this Expression retrieve the max quantity of the department, I need the quantity of the rep in Expression 1

• ###### Re: FirstSortedValue with  set analysis

Then try with Sum instead of Max (Mentioned by Mark).

• ###### Re: Best Rep. table

try like:

=sum({<REP_NAME={'*'}>} quantity)

• ###### Re: Best Rep. table

hi , it gives me the dimension quantity (department),

I need the reps quantity from expression 1

• ###### Re: Best Rep. table

Change the dimension, take REP_NAME as Dim and Expression wud be =sum(Quantity)

• ###### Re: Best Rep. table

Hi,

Is it not similar to what Tamil suggested, just replace the max with sum

sum(aggr (sum ( Quantity ), DEPARTMENT, REP_NAME ) )

Mark

• ###### Re: Best Rep. table

hi, thank you for your replay,

I tried it with sum, it gives the total quantity of the dimension (department)

• ###### Re: FirstSortedValue with  set analysis

Hi,

Sorry that me missing understanding.

So what we are trying to achieve is the quantity sold by the best sales person per department?

Do we have some sample data to play with? It is going to be a nested aggregation.

Mark

• ###### Re: FirstSortedValue with  set analysis

you are correct.

the quantity sold by the best sales person per department.

attached sample to source post

thank you

• ###### Re: FirstSortedValue with  set analysis

not sure but try

FirstSortedValue(Sum({<REP_NAME={"=Aggr(Sum(AMOUNT),DEPARTMENT, REP_NAME)"}>}QUANTITY), -Aggr(Sum(AMOUNT),DEPARTMENT, REP_NAME))

• ###### Re: FirstSortedValue with  set analysis

Within your application, where exactly are you wanting to use the required expression??? You have fully loaded application attached and its difficult to find the particular chart.