topic Re: Calculate scores bases on recent answer in QlikView

Calculate scores bases on recent answer

Thu, 23 Apr 2015 17:36:44 GMT

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

Re: Calculate scores bases on recent answer

sunny_talwar — Thu, 23 Apr 2015 17:44:28 GMT

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;

Re: Calculate scores bases on recent answer

sunny_talwar — Thu, 23 Apr 2015 17:45:36 GMT

Also find attached the sample app.

Best,

Sunny

Re: Calculate scores bases on recent answer

Thu, 23 Apr 2015 17:45:48 GMT

Is there a way to do it in the expression?

Re: Calculate scores bases on recent answer

sunny_talwar — Thu, 23 Apr 2015 17:50:12 GMT

Use the following expression:

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

HTH

Best,

Sunny

Re: Calculate scores bases on recent answer

Fri, 24 Apr 2015 15:45:02 GMT

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.

Re: Calculate scores bases on recent answer

sunny_talwar — Fri, 24 Apr 2015 16:07:22 GMT

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

Re: Calculate scores bases on recent answer

Sat, 25 Apr 2015 18:43:50 GMT

Thank you so much

Re: Calculate scores bases on recent answer

pratap6699 — Sun, 26 Apr 2015 15:00:37 GMT

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