Value based on MAX Timestamp

[chrisevans_cj]

Hi,

I'm trying to get the values of a field for the corresponding MAX Timestamp. This will be in straight chart. Below sample data:

DATECREATED	DEVICEID	RELEASENUMBER
2/9/2017 11:37:12 AM	660206241268677	1.0.0.6
31/8/2017 4:43:41 PM	660206241268677	1.0.0.5
31/8/2017 3:07:02 PM	660206241268677	1.0.0.5
31/8/2017 11:52:06 AM	867720587950660	1.0.0.5
31/8/2017 11:35:39 AM	867720587950660	1.0.0.5
31/8/2017 11:25:27 AM	867720587950660	1.0.0.5
31/8/2017 11:20:34 AM	867720587950660	1.0.0.5
31/8/2017 10:34:42 AM	867720587950660	1.0.0.5
31/8/2017 10:06:08 AM	660206241268677	1.0.0.5
31/8/2017 9:15:36 AM	660206241268677	1.0.0.5

The Straight Chart should be:

DEVICEID                    MAX(DATECREATED)          RELEASENUMBER

660206241268677           2/9/2017 11:37:12 AM                 1.0.0.6

867720587950660           31/8/2017 11:52:06 AM               1.0.0.5

Any advise?

[chrisevans_cj]

in the full dataset - there are lines which are repeated due to various products sold on a transaction. The items also have different prices - so to get the correct answer i need to exclude those in the expression so that the expression can return a single answer

[sunny_talwar]

Is this part of a field? What is that field name called?

[chrisevans_cj]

due to the multiple lines (Product and Product Line Price) some of the devices don't calculate the Releasenumber

[sunny_talwar · Accepted solution]

How about if you add DISTINCT

FirstSortedValue(DISTINCT RELEASENUMBER, -DATECREATED)

[chrisevans_cj]

Above to the left we have the straight chart. Please note the dash on the 2 line.

When looking at the data for the transaction which happened you will see that there are 6 products sold, which are either 10 or 20 in value. If I select and product - then the straight table shows the correct release version. The dash is due to multiple lines - so i need to ignore them to get the releasenumber

[chrisevans_cj]

Thank you Sunny - hope you have a great weekend

[effinty2112]

HI Chgris,

Maybe:

DEVICEID	Max(DATECREATED)	Concat(Aggr(if(Rank(DATECREATED,1,1)=1,RELEASENUMBER),DEVICEID,DATECREATED),',')
867720587950660	31/8/2017 11:52:06 am	1.0.0.5
660206241268677	2/9/2017 11:37:12 am	1.0.0.6