Show the average for the last 3 entries based on date

[Qlik_ULG]

Hi all,

I have a range of scores assigned to entries which also have associated fields, including a given date and time.

I am trying to get the average of the last 3 entries (based on latest Date_Times) for each item, but have been struggling to get the expression to return the correct result.

An example of my data table is as follows:

User	Field	Site	Date_Time	Score
1	Engineering	Spain	26/09/2018  03:38:19	156
1	Finance	US	27/09/2018  01:56:52	48
3	Engineering	Spain	27/09/2018  05:13:28	543
1	Finance	Germany	27/09/2018  05:14:17	28
1	Finance	Brazil	10/10/2018  17:57:20	91
3	Engineering	Japan	19/10/2018  03:55:58	872
1	Engineering	Spain	19/10/2018  07:28:09	34
2	Finance	South Africa	24/10/2018  11:00:44	654
2	Engineering	Japan	01/11/2018  05:29:40	85
1	Engineering	Spain	05/11/2018  09:49:25	157
1	Engineering	Spain	09/11/2018  14:23:13	321
3	Engineering	Mexico	11/11/2018  10:26:55	412
2	Finance	South Africa	12/11/2018  14:08:24	208

Essentially, this should produce the following result, based on the last 3 scores for this item:

User	Field	Site	Avg. Score
1	Engineering	Spain	170.67

I have been trying to use Avg(Aggr) based on Date_Time, but have not been able to get it apply to the latest 3.

Any assistance would be greatly appreciated.

Thanks very much.

[OmarBenSalem]

Based on your example; wouldn't the last 3 entries be:

1	Engineering	Spain	09/11/2018  14:23:13	321
3	Engineering	Mexico	11/11/2018  10:26:55	412
2	Finance	South Africa	12/11/2018  14:08:24	208

Am I missing sthing? Could u please explain the result?

User	Field	Site	Avg. Score
1	Engineering	Spain	170.67

This is the avg of what lines? and why?

[sunny_talwar]

---

@OmarBenSalem wrote:

Based on your example; wouldn't the last 3 entries be:

1	Engineering	Spain	09/11/2018  14:23:13	321
3	Engineering	Mexico	11/11/2018  10:26:55	412
2	Finance	South Africa	12/11/2018  14:08:24	208

Am I missing sthing? Could u please explain the result?

User	Field	Site	Avg. Score
1	Engineering	Spain	170.67

This is the avg of what lines? and why?

---

I think the average is from these three lines

1	Engineering	Spain	19/10/2018  07:28:09	34
1	Engineering	Spain	05/11/2018  09:49:25	157
1	Engineering	Spain	09/11/2018  14:23:13	321

[Qlik_ULG]

Thanks for the responses.

Correct. The User, Field and Site columns are listed as dimensions, so it is the average based on these as an 'Item' that I'm trying to return.

There are 4 entries for this item in the example data set.

User	Field	Site	Date_Time	Score
1	Engineering	Spain	26/09/2018  03:38:19	156
1	Engineering	Spain	19/10/2018  07:28:09	34
1	Engineering	Spain	05/11/2018  09:49:25	157
1	Engineering	Spain	09/11/2018  14:23:13	321

Based on the latest dates, the average of the last 3 is:

User	Field	Site	Avg. Score
1	Engineering	Spain	170.67

Thanks,

[sunny_talwar · Accepted solution]

---

@Qlik_ULG wrote:

Hi all,

I have a range of scores assigned to entries which also have associated fields, including a given date and time.

I am trying to get the average of the last 3 entries (based on latest Date_Times) for each item, but have been struggling to get the expression to return the correct result.

An example of my data table is as follows:

User	Field	Site	Date_Time	Score
1	Engineering	Spain	26/09/2018  03:38:19	156
1	Finance	US	27/09/2018  01:56:52	48
3	Engineering	Spain	27/09/2018  05:13:28	543
1	Finance	Germany	27/09/2018  05:14:17	28
1	Finance	Brazil	10/10/2018  17:57:20	91
3	Engineering	Japan	19/10/2018  03:55:58	872
1	Engineering	Spain	19/10/2018  07:28:09	34
2	Finance	South Africa	24/10/2018  11:00:44	654
2	Engineering	Japan	01/11/2018  05:29:40	85
1	Engineering	Spain	05/11/2018  09:49:25	157
1	Engineering	Spain	09/11/2018  14:23:13	321
3	Engineering	Mexico	11/11/2018  10:26:55	412
2	Finance	South Africa	12/11/2018  14:08:24	208

Essentially, this should produce the following result, based on the last 3 scores for this item:

User	Field	Site	Avg. Score
1	Engineering	Spain	170.67

I have been trying to use Avg(Aggr) based on Date_Time, but have not been able to get it apply to the latest 3.

Any assistance would be greatly appreciated.

Thanks very much.

---

Try this

Avg(Aggr(If(Date_Time >= Max(TOTAL <User, Field, Site> Date_Time, 3), Score), User, Field, Site, Date_Time))

[Qlik_ULG]

This is producing the correct results. Thanks very much.