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.
