2 Replies Latest reply on Aug 13, 2018 6:35 PM by John LaPlante

    Select Latest Date in a Month

    John LaPlante

      Hi all!


      I'm trying to display the latest enrollment data in each of the past months.  Here's what it looks like:



      The dates on the points are the latest weekly data in each of the months.  I want to only show the distinct count of Student ID for each of these weeks.  For example, for the last month, May, there are 3 sets of students loaded...one set for 5/7/2018, one for 5/14/2018, and one for 5/21/2018.  5/21/2018 is the latest week in that month, so that is the only data I want for that month.  I then want a COUNTD (unique count) of StudentNumber to show me the unduplicated head count.


      I created a calculated field called "MaxWeek" that is this formula:




      When I add it to the Label shelf, it turns into AGG(MaxWeek).  So far so good.


      But if I try to create a calculated field called "IsLatestWeek" (to be used on the filter shelf) like this:


      [Week] = [MaxWeek]


      I get an error about aggregate and non-aggregate fields can't be compared.


      So I'm stuck.


      Any ideas?


      Thanks so much!!