Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
gabriel_palucos
Contributor III
Contributor III

Get Max Date and Max Value of a ID

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

Table:  

IDDateValue
101/01/201610
101/01/201615
102/01/20165
102/01/20168
205/01/201625
205/01/20164
207/01/201619
207/01/201615

Result:

   

IDMax DateMax Value
102/01/20168
207/01/201619

Best Regards.

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Dimension:

ID

Expression:

1) Date(Max(Date))

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


Capture.PNG

View solution in original post

8 Replies
sunny_talwar

Try this:

Dimension:

ID

Expression:

1) Date(Max(Date))

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


Capture.PNG

nehasharma1006
Contributor III
Contributor III

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.

sunny_talwar

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)

atitachat_08
Contributor
Contributor

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

Thanks in advance

wiz_nerd
Creator
Creator

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_Stringdateoutput_string
string1date1string1_yyw3
string1date3string1_yyw2
string1date3string1_yyw1
string2date1string2_yyw1
string2date3string2_yyw2
string2date3string2_yyw3
wiz_nerd
Creator
Creator

Check the qvw attached
gauravgg
Partner - Creator
Partner - Creator

Hi sunny ,

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

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