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





      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!