2 Replies Latest reply on Sep 11, 2018 5:50 AM by Andy Hulsey

    Sum rows only with most recent date

    Andy Hulsey

      In short, all I need to do is sum rows with the most recent date.

       

      In long, I know this is likely to be simple, but for some reason I cannot accomplish it.

       

      What I have is a set of records updated only when there is new data, which is not daily. Typically, there are no new records on Saturdays or Sundays, or on certain holidays. When there are no new records, there are no records sent across, so I don't have a zero when there are no new records.

       

      My goal is to show the most recent data, which I can do, but I also need to perform some calculations based on the most recent data.

       

      In the attached workbook, I have some Web sessions data where I am able to accomplish the requirement. I get the Web sessions data daily and a zero will come across as zero. On the other hand, I have calls data which only comes across when it's non-zero.

       

      The requirement is to take the sum of calls from the most recent day and compare them to the average for the same day of the week for this year. I have this implemented for the Web sessions in the attached workbook, but I cannot do the same for the calls.

       

      At this point, the bottleneck is that I cannot sum the calls from the most recent day. In the Web sessions data, a day is a single row. In the calls data, a day can have multiple rows based on the reason for the call.

       

      You may have guessed by now that I am trying to take the MAX([Date]), compare it to [Date] and then sum rows when the dates are equal, which of course I cannot do because this is mixing aggregate and non-aggregate expressions.

       

      Does anyone have any thoughts on how best to proceed?

       

      Thank you,

       

      Andy