Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to calculate the average of the first and the last record in each group described in the example below.
An average of first/last "A" and first Last "B"
Example
Name | Sequence | Value |
A1 | 1 | 10 |
A2 | 2 | 20 |
A3 | 3 | 15 |
A4 | 4 | 10 |
A5 | 5 | 20 |
Name | Sequence | Value |
B1 | 1 | 15 |
B2 | 2 | 10 |
B3 | 3 | 10 |
Name | Sequence | Value |
A6 | 1 | 5 |
A7 | 2 | 20 |
A8 | 3 | 30 |
A9 | 4 | 15 |
Name | Sequence | Value |
B4 | 1 | 30 |
B5 | 2 | 5 |
B6 | 3 | 10 |
B7 | 4 | 20 |
First A : AVG(10+5)
First B: AVG(15+30)
Last A: AVG(5+15)
Last B: AVG(20+20)
I can't figure out how to find the last record in a group and afterwards find the average of all the last records.
Hope somebody can help me with an answer.
Hi,
what I wanted to say was, how could we tell QV which Names belong to a subgroup? Sequence numbers are not unique.
I assume your names are ascending with Sequence numbers, so if e.g. A6 has a lower sequence number than A5, this will indicate that it belongs to a different subgroup, starting a new sequence numbering.
I think if we had something like a subgroup index, that would make life much easier, since we can look for min and max sequence then per subgroup index.
I assume you don't have this in your model, so let's create an index:
Groups:
LOAD * INLINE [
Name, Sequence, Value
A1, 1, 10
A2, 2, 20
A3, 3, 15
A4, 4, 10
A5, 5, 20
B1, 1, 15
B2, 2, 10
B3, 3, 10
A6, 1, 5
A7, 2, 20
A8, 3, 30
A9, 4, 15
B4, 1, 30
B5, 2, 5
B6, 3, 10
B7, 4, 20
];
LOAD *,
Left(Name,1) as Group,
if (peek(Sequence)=Sequence-1 and previous(Left(Name,1))=Left(Name,1), peek(Subgroup), rangesum(peek(Subgroup))+1) as Subgroup
resident Groups order by Name asc;
I assumed that your Groups are given by the first letter, probably that is simplying your real situation, so just use anything else to retrieve your Groups here (Group means the class for which you want to average the results later on). For example, use Keepchar(Name) instead of left().
Now we are having Groups and subgroups, we can calculate the average of first and last sequences per subgroup and within groups like
=avg(aggr(FirstsortedValue(Value,Sequence),Group,Subgroup))
for averaging the first sequence number values and
=avg(aggr(FirstsortedValue(Value,-Sequence),Group,Subgroup))
for averaging the last sequence number values.
(See also attached app. I put above two expression in a straight table with dimension Group).
Regards,
Stefan
for last recrd use
set last_var=peek('fieldname',-1,'tablename')
fisrt recard
set First_var=peek('fieldname',0,'tablename')
and then in expression
(last_var +First_var)/2
Thank You for the answer.
My problem is not to find the average between the first and the last record but the average of all the first records or all the last records named A or B
Ex
Find the last record in group A1-A5
Find the last record in group A6-A9
Find the last record in group Ax-Ay
Find the average of all the results.
Hi Jesper,
is there a way that you can determine the Group / Subgroup of records?
I.e.
A1-A5 could be Group A, subgroup 1
A6-A9 Group A, subgroup 2
B1-B3 Group B, subgroup 1
B4-B7 Group B, subgroup 2
etc..
I think you could use something like Left(Name,1) for Group and somehow the restart of indexing for subgroup increment, but do you have this information maybe already in your model?
Regards,
Stefan
Hi Stefan
My Groups/Subgroups are well defined.
I have thousands of groups named A,
thousands of groups named B,
thousands of groups named C etc.
Each group have about 50 records.
I want to take the last record (defined bu sequence number) of each A group and then find the average of these records.
Regards
Jesper
Hi,
what I wanted to say was, how could we tell QV which Names belong to a subgroup? Sequence numbers are not unique.
I assume your names are ascending with Sequence numbers, so if e.g. A6 has a lower sequence number than A5, this will indicate that it belongs to a different subgroup, starting a new sequence numbering.
I think if we had something like a subgroup index, that would make life much easier, since we can look for min and max sequence then per subgroup index.
I assume you don't have this in your model, so let's create an index:
Groups:
LOAD * INLINE [
Name, Sequence, Value
A1, 1, 10
A2, 2, 20
A3, 3, 15
A4, 4, 10
A5, 5, 20
B1, 1, 15
B2, 2, 10
B3, 3, 10
A6, 1, 5
A7, 2, 20
A8, 3, 30
A9, 4, 15
B4, 1, 30
B5, 2, 5
B6, 3, 10
B7, 4, 20
];
LOAD *,
Left(Name,1) as Group,
if (peek(Sequence)=Sequence-1 and previous(Left(Name,1))=Left(Name,1), peek(Subgroup), rangesum(peek(Subgroup))+1) as Subgroup
resident Groups order by Name asc;
I assumed that your Groups are given by the first letter, probably that is simplying your real situation, so just use anything else to retrieve your Groups here (Group means the class for which you want to average the results later on). For example, use Keepchar(Name) instead of left().
Now we are having Groups and subgroups, we can calculate the average of first and last sequences per subgroup and within groups like
=avg(aggr(FirstsortedValue(Value,Sequence),Group,Subgroup))
for averaging the first sequence number values and
=avg(aggr(FirstsortedValue(Value,-Sequence),Group,Subgroup))
for averaging the last sequence number values.
(See also attached app. I put above two expression in a straight table with dimension Group).
Regards,
Stefan
Hi Stefan
Your suggestion seems to work for me.
Thanks a lot for your help.
Best Regards
Jesper