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:

       

      MAX([Week])

       

      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!!