First Sorted Value - Repeat Rows / Rank Issue

[Anonymous]

Dear all,

I have found most of the solution to the below problem from other community pages but am struggling with the final part, help would be much appreciated!

I am trying to create a table which shows the sequence of investment by clients using a historical track record month by month. The first part of the challenge was to remove repeat values (using DISTINCT in FirstSortedValue) but now I have the issue that in some months there are two new investments and I think the FSV is unable to tell between them. I would be happy to rank them within the month by the size but this is where I fail.

Abridged example of the input data (in bold example of the first situation where there is a repeat value)

CLIENT	MONTH	PROD	AMOUNT
ACCOUNT	01/05/2016	EQ	211,720
ACCOUNT	01/06/2016	CM	1,312,114
ACCOUNT	01/06/2016	EQ	582,959
ACCOUNT	01/06/2016	FI	1,300,500
ACCOUNT	01/07/2016	CM	1,267,931
ACCOUNT	01/07/2016	EQ	604,172
ACCOUNT	01/07/2016	FI	1,296,814
ACCOUNT	01/08/2016	CM	1,268,735
ACCOUNT	01/08/2016	EQ	629,561
ACCOUNT	01/08/2016	FI	1,293,994
ACCOUNT	01/09/2016	CM	1,269,440

In setting this up I created a sub table with just the min values which makes me thing the rank is the problem based on RANK function

CLIENT	PROD	SUM(AMOUNT)	MIN_MONTH	RANK
ACCOUNT	EQ	7,643,942	01/05/2016	1
ACCOUNT	CM	27,634,989	01/06/2016	2-3
ACCOUNT	FI	26,143,488	01/06/2016	2-3
ACCOUNT	MF	3,863,171	01/10/2016	4

As an output I am trying to create a new table with the first, second and third investments made, using the code:

First Value Example:

FirstSortedValue(

DISTINCT PROD,

AGGR(MIN(MONTH),PROD, CLIENT),

1

)

Second Value Example:

FirstSortedValue(

DISTINCT PROD,

AGGR(MIN(MONTH),PROD, CLIENT),

2

)

etc.

So in this example, I would expect the result table to look like this:

CLIENT	First Invest	Second Invest	Third Invest	Fourth Invest
ACCOUNT	EQ	CM	FI	MF

I may be totally off track and there is an easier way to do this!

Thanks,

Ben

[sunny_talwar · Accepted solution]

May be try like this

FirstSortedValue(DISTINCT PROD, Aggr(((Min(MONTH)*1E2)-(Sum(AMOUNT)/1E10)),PROD, CLIENT), 2)

[Anonymous]

Thanks Sunny - that does work!

Quick question though - is the use of 1E2 and 1E10 by way of scaling the results so when they are combined you get the correct order? I.e. 1E2 makes the month figure large and /1E10 makes the amount small?

[sunny_talwar]

Quick question though - is the use of 1E2 and 1E10 by way of scaling the results so when they are combined you get the correct order? I.e. 1E2 makes the month figure large and /1E10 makes the amount small?

You are absolutely correct .