Values per Max Date for ID

[Anonymous]

Hi All,

I am working with numerous IDs and I want to return 2 different pieces of information

1) The Max Date of the Last Actual Data Received

2) The Max Date of the Last Estimated Data Received

I have tried the below expression with ID as the dimension but it only returns a list of all IDs with an Actual Value for the 22/01/2018 (Being the Max Date on the Report)

Only({<DATA_VALUE={ACTUAL}, DATE_TIME_DAY={'$(=max(READ_DATE_TIME_DAY))'}>} DATE_TIME_DAY)

However it should also return sites who's last date of Actual Data is less than the 22/01/2018 as well.

The estimated part is something similar as it is using the same expression:

Only({<DATA_VALUE-={ACTUAL}, DATE_TIME_DAY={'$(=max(READ_DATE_TIME_DAY))'}>} DATE_TIME_DAY)

Any help is greatly appreciated,

Thanks,

Tim

[sunny_talwar · Accepted solution]

May be try this

FirstSortedValue({<DATA_VALUE = {'ACTUAL'}>} DATE_TIME_DAY, -READ_DATE_TIME_DAY)

FirstSortedValue({<DATA_VALUE -= {'ACTUAL'}>} DATE_TIME_DAY, -READ_DATE_TIME_DAY)

[Anonymous]

Hi Sunny,

Works brilliant.

Thank You!

[Anonymous]

Hi Sunny,

Further to this I have came across several exceptions where we have received two different sets of Non Actual Data on the same day. This is currently returning blank.

How would I amend your expressions so it also only returns the non actual with the highest VERSION number?

Thanks,

[sunny_talwar]

May be this

FirstSortedValue({<DATA_VALUE = {'ACTUAL'}>} DATE_TIME_DAY, -(READ_DATE_TIME_DAY+DATE_TIME_DAY/1E10))

FirstSortedValue({<DATA_VALUE -= {'ACTUAL'}>} DATE_TIME_DAY, -(READ_DATE_TIME_DAY+DATE_TIME_DAY/1E10))