Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
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.
No problem,
I usually put a lot of effort to get the script right in order to have expressions simple (where possible).
regards!