3 Replies Latest reply on Nov 8, 2018 12:22 PM by Jeevan Krishna

    Calculation to show value based off of MAX DATE

    Lauren Brown

      I have a calculation to display the last date an account was serviced.  That calculation is MAX(DATE([srvdat])) and it is giving me what I want.  I want to take it one step further and only show the 'action' that took place on that last date.  Right now, if I bring in the Service Action to my view, it shows both actions and the dates both of those actions took place. 

       

      Below is a screen shot of my data

       

       

      I only want the service action that was done on the date of last service to be displayed, not both actions and the last time both actions were done.

        • 1. Re: Calculation to show value based off of MAX DATE
          Emanual Henry

          Is this what you were going for?  It only displays the service action if it is the max date (which in this case there are two).

          Max Date Question.png

          1 of 1 people found this helpful
          • 2. Re: Calculation to show value based off of MAX DATE
            Joe Oppelt

            Some of this is going to depend on how your sheet and data are set up.   Are you using table calcs to grab that max date, for example?

             

            But in short, I would approach it like this:

             

            { FIXED [Account] : MAX([Service Date]) }

             

            (I notice that you are doing  MAX(DATE([srvdat])).  Is [srvdat] not a date field already?)

             

            And then  this;

             

            { FIXED [Account] : MAX( if [Service Date] = [MAX LOD DATE] then [srvact] END) }

             

            Then, for every account, you would have the [srvact] that occurred on the row where the max date was.


            Question:  What do you want to happen if two different [srvact]s occur on the same date for an account?

            1 of 1 people found this helpful
            • 3. Re: Calculation to show value based off of MAX DATE
              Jeevan Krishna

              Hi Lauren,

               

              When you include service action field in the view, the view granularity change to service action level since it is lower than account level.

               

              You need to find minimum date on an account level and try to extract service action based on the comparison.

               

              If [Date] = {Fixed [account]:Max([Date])

              then [serviceact] end

               

              Hope this helps

              1 of 1 people found this helpful