Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max Date Expression?

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>

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

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

Capture.PNG

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?

View solution in original post

16 Replies
MK9885
Master II
Master II

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

//You've time stamp as well to your last update field, so I think this should work.

if it was just date field Or maybe this

=Count({<course_id, [last_update_date,1] = {$(=Max([last_update_date,1))}>}transcript_status)



Not applicable
Author

These don't return the right date. In the example above ^ I'm looking for it to only keep the 6/27/2016 record. It keeps the 3/9/2016 with your expression. In addition - if populates all courses at that point. I want it to adjust based on a filtered selection.

MK9885
Master II
Master II

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


Can you post a sample?

aarkay29
Specialist
Specialist

may be this

Timestamp(max({<course_id={"=max([last_update_date,1])"}>}[last_update_date,1]))

Not applicable
Author

It doesn't like the expression - see error below in red.

aarkay29
Specialist
Specialist

Sorry

only({<transcript_status={"=Aggr(max([last_update_date,1]),course_id)"}>}transcript_status) for Status expression

sunny_talwar

May be try this

Dimension:

course_id

Expression:

FirstSortedValue(Transcript_Status, -last_update_date)

Max(last_update_date)

Not applicable
Author

See how I am filtered on 1 course_id? With your expression - it forces all course_id and ignores the filter. In addition - the expression only shows '1'

sunny_talwar

Remove the square brackets from here:

Capture.PNG

but I would suggest not to use Set analysis here and use FirstSortedValue