topic Re: Get Max Date and Max Value of a ID in QlikView

Get Max Date and Max Value of a ID

Anonymous — Mon, 11 Apr 2016 18:04:46 GMT

It's possible to get a max value of a max date of one ID using expressions in QlikView?

Table:

ID	Date	Value
1	01/01/2016	10
1	01/01/2016	15
1	02/01/2016	5
1	02/01/2016	8
2	05/01/2016	25
2	05/01/2016	4
2	07/01/2016	19
2	07/01/2016	15

Result:

ID	Max Date	Max Value
1	02/01/2016	8
2	07/01/2016	19

Best Regards.

Re: Get Max Date and Max Value of a ID

sunny_talwar — Mon, 11 Apr 2016 18:07:20 GMT

Try this:

Dimension:

Expression:

1) Date(Max(Date))

2) FirstSortedValue(Value, -(Date* 10000 + Value))

Re: Get Max Date and Max Value of a ID

Anonymous — Thu, 28 Apr 2016 15:16:37 GMT

Hi Sunny,

Great solution .. However I didn't understand, in the FirstSortedValue function, why would we need to use (Date*1000+Value) as sort weight .. I was assuming it would sort just by using 'Date' here.

Could you please explain this logic.. I am quite confused.

Re: Get Max Date and Max Value of a ID

sunny_talwar — Thu, 28 Apr 2016 16:04:44 GMT

Trying to sort just based on date will give you two max values for each ID. But we want the max value for max date. This is double sorting, which isn't available within FirstSortedValue. So in order to do double sorting, we are trying to sort based on Date 1st (mutiplying is by 10000 to give it higher wait) and second by Value itself (multiplying by 1 to give it lower weight)

Re: Get Max Date and Max Value of a ID

atitachat_08 — Mon, 21 May 2018 09:46:29 GMT

I had try to see from your source but i cant found anything. Could you kindly help to explain

Thanks in advance

Re: Get Max Date and Max Value of a ID

wiz_nerd — Fri, 18 Jan 2019 13:42:08 GMT

I have a similar question. I am trying to create a calculated dimension in which I need to consider (distinct max date and distinct Name_String) ?

Name_String	date	output_string
string1	date1	string1_yyw3
string1	date3	string1_yyw2
string1	date3	string1_yyw1
string2	date1	string2_yyw1
string2	date3	string2_yyw2
string2	date3	string2_yyw3

Re: Get Max Date and Max Value of a ID

wiz_nerd — Wed, 30 Jan 2019 12:51:00 GMT

Check the qvw attached

Re: Get Max Date and Max Value of a ID

gauravgg — Thu, 07 Feb 2019 11:15:00 GMT

Hi sunny ,

Again ,if i have to take value of max date form expression 2 , how to do ?

Re: Get Max Date and Max Value of a ID

Simran012 — Fri, 28 Jan 2022 08:11:13 GMT

Hi @sunny_talwar i Used this solution, it is working fine for most of the IDs But i am getting Null in some case as as shown below

For Example:

Date	Time	ID	Value
1/12/2021	1:51:35 PM	101	1
1/12/2021	10:13:33 PM	101	2
1/12/2021	10:13:34 PM	101	2

It should return value 2 with time 10:13:34 as it is the max value but its showing null, i think the function cannot able to identify which one is higher because 2 values are same.

Kindly advise