Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

ksmccann
New Contributor III

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

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.

Tags (1)
1 Reply

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

You didn't say what your chart dimensions were, but you can usually get this using FirstSortedValue:

=FirstSortedValue(ResponseDescription, SurveyDate)  // First value

=FirstSortedValue(ResponseDescription, SurveyDate, -1)  // Last value

-Rob

http://robwunderlich.com

Community Browser