16 Replies Latest reply: Feb 28, 2017 11:25 AM by Sunny Talwar RSS

    Max Date Expression?

    Lauren Mills

      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>

       

        • Re: Max Date Expression?
          Aehman K

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


           


          • Re: Max Date Expression?
            Sunny Talwar

            May be try this

             

            Dimension:

            course_id

             

            Expression:

            FirstSortedValue(Transcript_Status, -last_update_date)

            Max(last_update_date)

              • Re: Max Date Expression?
                Lauren Mills

                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?

                 

                  • Re: Max Date Expression?
                    Sunny Talwar

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

                      • Re: Max Date Expression?
                        Lauren Mills

                        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.

                          • Re: Max Date Expression?
                            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?