Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
Hi Sunny,
Works brilliant.
Thank You!
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,
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))