Set analysis: Pivot table and First Sorted Value and Aggr Function

[alvarogh27]

Dear Friends:

I have a question about the utilization of First Sorted Value combinated with Aggr Función in a PivotTable.

I have 2 cases:

1-To know the best agent Agent with more sales for month and rute.

2-To know the sum of sales of best agent for month and route.

In the first case, i solve with the expression into the PivotTable:

FirstSortedValue(AGENT, -(Aggr(Sum(SALES),REGION, RUTE, MONTH,YEAR)))

It's like this:

AÑO	MES	RUTA	RUTA-01	RUTA-02	RUTA-03	RUTA-04	RUTA-05	RUTA-06	RUTA-07	RUTA-08	RUTA-09
2015	ENERO		AGENT 3	AGENT 6	AGENT 22	AGENT 25	AGENT 9	AGENT 25	AGENT 1	AGENT 7	AGENT 25
2015	FEBRERO		AGENT 3	AGENT 11	AGENT 19	AGENT 5	AGENT 6	AGENT 14	AGENT 9	AGENT 22	AGENT 24
2015	MARZO		AGENT 12	AGENT 22	AGENT 2	AGENT 10	AGENT 13	AGENT 16	AGENT 10	AGENT 17	AGENT 11
2015	ABRIL		AGENT 1	AGENT 8	AGENT 12	AGENT 8	AGENT 6	AGENT 13	AGENT 6	AGENT 1	AGENT 19
2015	MAYO		AGENT 0	AGENT 16	AGENT 9	AGENT 24	AGENT 21	AGENT 4	AGENT 16	AGENT 25	AGENT 25
2015	JUNIO		AGENT 8	AGENT 16	AGENT 14	AGENT 5	AGENT 18	AGENT 10	AGENT 2	AGENT 16	AGENT 3
2015	JULIO		AGENT 17	AGENT 1	AGENT 7	AGENT 11	AGENT 25	AGENT 21	AGENT 6	AGENT 11	AGENT 16
2015	AGOSTO		AGENT 25	AGENT 22	AGENT 3	AGENT 0	AGENT 20	AGENT 7	AGENT 4	AGENT 18	AGENT 0
2015	SEPTIEMBRE		AGENT 3	AGENT 7	AGENT 5	AGENT 25	AGENT 15	AGENT 3	AGENT 2	AGENT 16	AGENT 1
2015	OCTUBRE		AGENT 11	AGENT 3	AGENT 22	AGENT 0	AGENT 1	AGENT 8	AGENT 17	AGENT 3	AGENT 5
2015	NOVIEMBRE		AGENT 25	AGENT 21	AGENT 18	AGENT 5	AGENT 6	AGENT 11	AGENT 17	AGENT 21	AGENT 14
2015	DICIEMBRE		AGENT 18	AGENT 9	AGENT 11	AGENT 0	AGENT 21	AGENT 0	AGENT 9	AGENT 15	AGENT 24
2016	ENERO		AGENT 5	AGENT 25	AGENT 20	AGENT 12	AGENT 20	AGENT 0	AGENT 16	AGENT 22	AGENT 17
2016	FEBRERO		AGENT 7	AGENT 12	AGENT 8	AGENT 8	AGENT 4	AGENT 18	AGENT 4	AGENT 21	AGENT 11
2016	MARZO		AGENT 7	AGENT 10	AGENT 16	AGENT 4	AGENT 22	AGENT 22	AGENT 8	AGENT 11	AGENT 21
2016	ABRIL		AGENT 21	AGENT 21	AGENT 5	AGENT 11	AGENT 22	AGENT 14	AGENT 0	AGENT 14	AGENT 15
2016	MAYO		AGENT 14	AGENT 3	AGENT 18	AGENT 25	AGENT 21	AGENT 13	AGENT 6	AGENT 18	AGENT 7
2016	JUNIO		AGENT 10	AGENT 15	AGENT 5	AGENT 6	AGENT 9	AGENT 1	AGENT 23	AGENT 8	AGENT 7
2016	JULIO		AGENT 2	AGENT 18	AGENT 5	AGENT 1	AGENT 5	AGENT 11	AGENT 21	AGENT 18	AGENT 9
2016	AGOSTO		AGENT 3	AGENT 10	AGENT 6	AGENT 0	AGENT 23	AGENT 19	AGENT 12	AGENT 15	AGENT 6

In the other case:

I tried this:

1-Sum({$<AGENT={'=FirstSortedValue(AGENT,  -(Aggr(Sum(SALES),REGION, RUTE, MONTH,YEAR))))'}>} SALES)

2-Sum({$<AGENT={"=FirstSortedValue(AGENT,  -(Aggr(Sum(SALES),REGION, RUTE, MONTH,YEAR))))"}>} SALES)

3-Sum({$<AGENT={'$(=FirstSortedValue(AGENT,  -(Aggr(Sum(SALES),REGION, RUTE, MONTH,YEAR)))))'}>} SALES)

It does'nt work anything of 3 expression.

I don't understand if it works the FirsSortedValue Part alone, i can't use like a set analysis in the second case.

Thank's

[sunny_talwar]

Have you tried this?

Max(Aggr(Sum(SALES), REGION, RUTE, MONTH, YEAR))

[alvarogh27]

Hi Sunny:

Thanks for your faster answer.

I tried this and it doesn't work.

I use FirstSortedValue because it's possible  may be there are several agent with the same sales and I only want one.

In the first case it's working, but when i insert this part inside the second case....it doesn't work.

[sunny_talwar]

I am not entirely sure I understand... would you be able to share some raw data with the expected output from it to get a better feel for your question?

[alvarogh27]

I send you a *.qvw and *.xls whith the data.

[sunny_talwar · Accepted solution]

See if this is what you want

=Sum(Aggr(If(AGENTE = FirstSortedValue(TOTAL <REGION, FECHA, MES, RUTA> AGENTE, -Aggr(Sum(SALES), REGION, FECHA, MES, RUTA, AGENTE)), Sum(SALES)), REGION, FECHA, MES, RUTA, AGENTE))

[alvarogh27]

IT' WORKS!!!!!

Thanks very match.

Now I just have to understand.