topic Re: Best Rep. table in QlikView

FirstSortedValue with set analysis

ilanbaruch — Wed, 11 Nov 2015 17:23:39 GMT

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

sunny_talwar — Wed, 11 Nov 2015 17:28:08 GMT

May be try this:

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

Re: Best Rep. table

ilanbaruch — Thu, 12 Nov 2015 07:15:38 GMT

did not work

: (

Re: Best Rep. table

tamilarasu — Thu, 12 Nov 2015 07:21:14 GMT

Like below!!

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

Re: Best Rep. table

Anonymous — Thu, 12 Nov 2015 07:23:31 GMT

try like:

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

Re: Best Rep. table

ilanbaruch — Thu, 12 Nov 2015 07:29:39 GMT

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

I need the reps quantity from expression 1

Re: Best Rep. table

Anonymous — Thu, 12 Nov 2015 07:33:48 GMT

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

Re: Best Rep. table

ilanbaruch — Thu, 12 Nov 2015 07:43:45 GMT

hi,

thank you for your effort,

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

Re: Best Rep. table

Mark_Little — Thu, 12 Nov 2015 08:28:13 GMT

Hi,

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

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

Mark

Re: FirstSortedValue with set analysis

tamilarasu — Thu, 12 Nov 2015 08:34:58 GMT

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

Re: Best Rep. table

ilanbaruch — Thu, 12 Nov 2015 08:43:27 GMT

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

Mark_Little — Thu, 12 Nov 2015 08:57:38 GMT

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

Kushal_Chawda — Thu, 12 Nov 2015 09:23:30 GMT

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

ilanbaruch — Thu, 12 Nov 2015 09:36:32 GMT

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

sunny_talwar — Thu, 12 Nov 2015 11:15:43 GMT

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.