Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jadewind
Contributor

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_miszkiewic
Honored Contributor III

Re: average of earliest values of repeaters

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

3 Replies
lech_miszkiewic
Honored Contributor III

Re: average of earliest values of repeaters

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

jadewind
Contributor

Re: average of earliest values of repeaters

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_miszkiewic
Honored Contributor III

Re: average of earliest values of repeaters

No problem,

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

regards!