Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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>
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?
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?
You don't want transcript_status as your dimension. Remove it from your dimension and use the FirstSortedValue() expression for transcript_status
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.
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?
You are so smart - you always know how to help me I appreciate you! Thank you Sunny!!!!
Hahahaha ya, I am your Qlik Community best friend