topic Re: Values per Max Date for ID in QlikView

Values per Max Date for ID

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Values per Max Date for ID

sunny_talwar — Thu, 25 Jan 2018 12:23:53 GMT

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)

Re: Values per Max Date for ID

Anonymous — Thu, 25 Jan 2018 12:27:39 GMT

Hi Sunny,

Works brilliant.

Thank You!

Re: Values per Max Date for ID

Anonymous — Fri, 26 Jan 2018 11:49:40 GMT

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,

Re: Values per Max Date for ID

sunny_talwar — Fri, 26 Jan 2018 11:57:49 GMT

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))