create variable or expression

[drohm002]

Hi, please see table below.  How can I create a variable or an expression that returns only one score per company, based on the earliest date?

Thank you.

ID	Score	Date	Company
bill	4	1/1/2016	Blue
bill	7	6/1/2016	Blue
sam	8	2/1/2016	Blue
sam	2	7/1/2016	Blue
jon	9	3/1/2016	red
jon	3	8/1/2016	red
adam	11	5/1/2016	red
adam	6	7/1/2016	red

[martinpohl]

Do it in the script:

load YourData;

FirstScore:

load distinct Company

resident YourData;

left join load

Company,

min(Date) as FirstDate

resident YourData

Group by Company;

left join load

Company,

Date as FirstDate,

Score as FirstScore

resident YourData;

drop field FirstDate;

Regards

[drohm002]

is it possible to not do it in the script?  the company field is from a different table than all the other fields so I'm having trouble using Company in the script...

[drohm002]

I have a flag set up to find the min date.  can we use that in a variable or expression to help us?

[andrey_krylov]

Hi Daniel. Maybe FirstSortedValue(TOTAL <Company> Score, Date)

[drohm002]

is TOTAL a function?

[sergio0592]

TOTAL is not a function. It's a key word which can be explained with :

The TOTAL qualifier is added to aggregation functions to disregard chart
dimensions and make the calculation over the entire record set as defined
by the current selection state.