Max Date Expression?

[#1]

I have a course_id (17683) and two records attached to the course_id by Transcript_Status. I only want to show the MAX (or most recent Transcript_Status) based on the last date possible. I thought I could just do a Max(Last_Update_Date,1) to get it but it doesn't work. Thoughts on how to write this expression>

[#2]

Hi Sunny,

We're getting closer - maybe I read your post wrong -

The FirstSortedValue bring my current date to the top. But the max(last_update_date) isn't doing anything - I don't think. Are they supposed to be in the same expression?

[MK9885]

Sorry, not an expert in Set Expressions.

But can you explain this... I only want to show the MAX (or most recent Transcript_Status) based on the last date possible. ??

and use

=Only({<course_id, [last_update_date,1] = {">=Floor([last_update_date,1] ) = Today()"}>}transcript_status)

My expression will give you the dates which are greater than your minimum date from today.

stalwar1‌ can probably help you out?

[sunny_talwar]

You don't want transcript_status as your dimension. Remove it from your dimension and use the FirstSortedValue() expression for transcript_status

[#5]

Okay - Let's try this...

(Please note - I am filtered on employee_id but I can share that in the screen view for privacy reasons)

We are looking at course_id for each employee_id. They can have multiple transcript_status per course_id. But I only want to show the row that has the most current last_update_date attached to it.

1. I tried this expression originally 

=date(max(last_update_date,DateFormat='M/D/YY'))

^ Saying give me the max date of the last_update_date and I am formatting accordingly. However, I know that I need to work set analysis into this to point specifically at the employee_id and course_id. I am struggling to do this.

2. You suggested I try -

FirstSortedValue(transcript_status, -last_update_date)

^You see that this has potentially sorted them accordingly - but I don't want to reference the 'cancel' in this case because it is attached to an older date.

I hope this cleared up some questions in regards to my issues.

[sunny_talwar · Accepted solution]

Let me try this one more time also..... Right now you have two dimensions

1) course_id

2) transcript_status

and two expression

1) Max(....)

2) FirstSortedValue(...)

So far so good?

All I want you to do is to remove transcript_status as your dimension. So you will end up with this

1) course_id

2 Expressions

1) Max(....)

2) FirstSortedValue(...)

This should give you only 1 row which would be the scheduled row. If this isn't what you want, then I guess I have some serious understanding issues .

The problem is that your expressions show information based on the unqiue combination of your dimensions. As long as you have transcript_status as a dimension, it will continue to show Max date based on the combination of course_id and trasncript_status. As soon as you remove it, you will get the max date based on course_id and you will use FirstSortedValue to pull the status on the max date.

Does that make sense?

[#7]

You are so smart - you always know how to help me I appreciate you! Thank you Sunny!!!!

[sunny_talwar]

Hahahaha ya, I am your Qlik Community best friend