2 Replies Latest reply on Nov 1, 2019 1:56 PM by Abdul Munaf

    using filters with if [date]={max([date])} then...

    Jessica Tepper

      I need to create a calculated field that will return a value when the date field is = max(date).  I've tried using {} to create a LOD expression, but since I'm using a filter and the data set contains future dates, that doesn't work for what I need. 

       

      The data of interest for this specific problem looks like this:

       

      DateEvent Name
      Size
      2019-09-0119GABC

      15000

      2019-09-1519GDEF16500
      2019-10-0119GHIJ17000
      2019-10-1519GKLM16500
      2019-11-0119GNOP
      2019-11-1519GQRS
      2019-12-0119GTUV

       

       

      I'm filtering on event name to only include 19GDEF, 19GHIJ, and 19GKLM.  Ultimately I want to be able to compare the size of 19GKLM to the average of 19GDEF and 19GHIJ.  And as more events occur, to adjust so that the most recent event is being compared to the average of the previous events (but always excluding 19GABC). 

       

      I thought I could use an if then statement to select the size for the most recent event and another to select the average size for the other events.  For the first part, what I came up with was:

       

      if [Date]={max([Date])} then [Size].

       

      I was using the {} to get around have aggregate and non-aggregates in the same statement; but since I'm turning the max([Date]) into an LOD, then the filter no longer gets applied.  I took a look at this discussion:Show Only Values From Max Date in Dataset , but again, am hoping to find a solution where the filter will still be applied

       

      Is there a way to do this?

       

      Also, apologies for not including a workbook, but the data is of a sensitive nature and can't be shared. 

      Thanks so much!