Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

average of earliest values of repeaters

Hi everyone,

I have a transaction table where one ID can have multiple transactions on different dates (there are of course IDs that only have single transactions).

I am hoping to get the average of the earliest values of each repeater IDs. For example ID1 had three transactions on three different dates costing different $s, ID2 had four transactions on four different dates costing different $s and so on. I want the average of the earliest $s of these IDs (and also average of latest $s and then calculate the difference from the average of earliest $s).

How do I do it?

I can now create a table to list IDs and Max(Date) and Min(Date) but don't know how to make the Average work. I just need the end results, like Average of earliest, Average of last, Difference.

Many thanks in advance for any help!

1 Solution

Accepted Solutions
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Again if you are able to identify records with Max  or Min date then flag those records in the load script and then when building expression use the flag in either If statement or Set analysis expresssion like:

Avg(If(Flag=1,Amount,Null())

or

Avg({<Flag={1]>}Amount)

please attach file for further investigations

kind regards

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

3 Replies
Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Again if you are able to identify records with Max  or Min date then flag those records in the load script and then when building expression use the flag in either If statement or Set analysis expresssion like:

Avg(If(Flag=1,Amount,Null())

or

Avg({<Flag={1]>}Amount)

please attach file for further investigations

kind regards

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Anonymous
Not applicable
Author

Thanks Lech for your instruction. It took me some time to learn it but I have managed to flag in the load script and build expressions to achieve what I wanted.

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

No problem,

I usually put a lot of effort to get the script right in order to have expressions simple (where possible).

regards!

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.