Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@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))
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?
@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 |
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,
@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))