Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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...
I have a flag set up to find the min date. can we use that in a variable or expression to help us?
Hi Daniel. Maybe FirstSortedValue(TOTAL <Company> Score, Date)
is TOTAL a function?
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 chartdimensions and make the calculation over the entire record set as defined
by the current selection state.