Calculate scores bases on recent answer

[#1]

Hi all,

my data looks as shown below

Subject	Question	Timestamp	Score
A	1	03/20/2015 7:07:21 PM	3
A	2	03/20/2015 7:06:31 PM	4
B	1	03/20/2015 7:06:16 PM	5
B	1	03/20/2015 7:05:59 PM	3
B	2	03/20/2015 7:05:40 PM	5
B	3	03/20/2015 7:05:02 PM	6

Basically a subject can have several questions, but there should be only one answer considered per question. If there were multiple answers recorded, we take the most recent response. The aim is to calculate the average score for each subject and display it in bar graph.

Subject A should say (3+4)/2 =3.5  Subject B should say (5+5+6)/3 = 5.33. However what is currently happening is subject B says (5+3+5+6)/4 and this is not correct.

I need to figure a way to not consider the row 4. I would also like to do it in the expressions and not disturb the data model as it is complex and this is just an example. Any help ?

My current expression says avg(score) with Subject as dimension.

Thanks,

Praveen

[sunny_talwar]

Try this script:

Table:

LOAD Subject,

     Question,

     Timestamp,

     Score

FROM

[https://community.qlik.com/thread/161497]

(html, codepage is 1252, embedded labels, table is @1);

Table1:

NoConcatenate

LOAD Subject,

  Question,

  FirstSortedValue(Score, -Timestamp) as Score,

  FirstSortedValue(Timestamp, -Timestamp) as TimeStamp

Resident Table

Group By Subject, Question;

DROP Table Table;

[sunny_talwar]

Also find attached the sample app.

Best,

Sunny

[#4]

Is there a way to do it in the expression?

[sunny_talwar]

Use the following expression:

=Avg(If(Aggr(FirstSortedValue(Question, -Timestamp), Subject, Question) = Aggr(Question, Subject, Question), Score))

HTH

Best,

Sunny

[#6]

Hi Sunny,

Thank you for your response. I tried the expression and it works but the problem is the consistency. It does not select the recent response if a subject has multiple questions and these questions received multiple answers.

In the example I have attached, the response to question 1 is recent for Subject B, but in case of subject C, it is incorrect.

[sunny_talwar · Accepted solution]

Hey Ravi,

Try this new expression: =Avg(Aggr(FirstSortedValue(Score, Timestamp), Question, Subject)) if you want to sum the first response or =Avg(Aggr(FirstSortedValue(Score, -Timestamp), Question, Subject)) if you want to sum the most recent response.

Best,

Sunny

[#8]

Thank you so much

[pratap6699]

=Avg(Aggr(FirstSortedValue(Score, Timestamp), Question, Subject))