Solved: Max Date Expression? - Qlik Community

Report Inappropriate Content · ‎2017-02-27

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>

sunny_talwar · ‎2017-02-28

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?

View solution in original post

MK9885 · ‎2017-02-27

=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)

Report Inappropriate Content · ‎2017-02-27

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 · ‎2017-02-27

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

Can you post a sample?

aarkay29 · ‎2017-02-27

may be this

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

Report Inappropriate Content · ‎2017-02-27

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

aarkay29 · ‎2017-02-27

Sorry

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

sunny_talwar · ‎2017-02-27

May be try this

Dimension:

course_id

Expression:

FirstSortedValue(Transcript_Status, -last_update_date)

Max(last_update_date)

Report Inappropriate Content · ‎2017-02-27

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 · ‎2017-02-27

Remove the square brackets from here:

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

Max Date Expression?

Related Topics