1 Reply Latest reply: Apr 25, 2012 10:30 PM by Rob Wunderlich RSS

    Need to display the first and last response in a set of records

    Kevin McCann

      Hi All

           I have a report that looks at survey responses, that will be organized by store, survey type and question. I need to show the first response based on the earliest date responded and the last response.

       

         I am able to display the correct dates in a table using the following functions:

       

      First Response Date

      aggr( min(total SurveyDate), StoreID, Questionid, SurveyTypeDescription )

       

      Last Response Date

      aggr( max( total SurveyDate), StoreID, Questionid, SurveyTypeDescription )

       

       

      My expression to get the last response works correctly and looks as follows:

       

      max(if(SurveyDate = aggr( max(SurveyDate), StoreID, Questionid, SurveyTypeDescription ) ,  ResponseDescription))

       

      However my min response function always returns nothing

      min(if((SurveyDate) = (aggr( MIN(total SurveyDate), StoreID, Questionid, SurveyTypeDescription )) ,   ResponseDescription))

       

      If I change the expression to be

      min(if((SurveyDate) =   MIN(total SurveyDate),   ResponseDescription))

       

      this works but does not pull store/survey/question specific data, it is the earliest date of all responses at all stores for all questions, which is not what I want.

       

      It seems as if the AGGR function does not calculate except on the last record of the set. If I add the survey date to my straight table then I see 2 rows for 1 store but the first row has nulls in my 2 expression fields, so it does not even display the Last response correctly on the earliest date record, only on the last record of the batch.

       

      Any help would be greatly appreciated with this as I am banging my head on a wall.