Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following sample table where I am required to calculate the average time for each parameter.
For ids where date closed is populated
Time = Date Closed – Date Open
For ids where date closed is blank
Time = max(Date Updated) – Date Open
Parameters | ID | Date Open | Date Closed | Date Updated |
Temperature | AB | 5/13/2017 | 12/16/2017 | 12/18/2017 |
Wind force | AD | 6/8/2018 | 10/10/2018 | |
Wind force | AD | 6/8/2018 | 11/10/2018 | |
Humidity | AC | 3/10/2018 | 1/2/2019 | 2/7/2019 |
Temperature | AG | 8/15/2018 | 11/20/2018 | |
Humidity | AH | 5/3/2018 | 8/19/2018 | |
Humidity | AH | 5/3/2018 | 9/2/2018 | |
Wind force | AO | 7/23/2018 | 7/29/2018 |
I am required to find the average time taken for each parameter and the no. of ids which are above the average time. My final Table should look something like this:
Cases | Average Time | No of IDs Above Average |
Temperature | 157 | 1 |
Humidity | 210.5 | 1 |
Wind force | 81 | 1 |
Try this for Count of IDs
Count(Aggr( If(Avg(TOTAL <Parameters> Aggr(If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), Parameters, ID)) > If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), ID) , ID, Parameters))
or
Count(DISTINCT Aggr( If(Avg(TOTAL <Parameters> Aggr(If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), Parameters, ID)) > If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), ID) , ID, Parameters))
May be try this
Avg(Aggr(
If(Len(Trim([Date Closed])) > 0,
([Date Closed] - [Date Open]),
(Max(TOTAL <ID> [Date Updated]) - [Date Open])
)
, Parameters, ID))
Hi Sunny,
Thanks for the response. However, I am looking to count the number of IDs against each parameter that are above the average calculated (last column of my result table).
Try this for Count of IDs
Count(Aggr( If(Avg(TOTAL <Parameters> Aggr(If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), Parameters, ID)) > If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), ID) , ID, Parameters))
or
Count(DISTINCT Aggr( If(Avg(TOTAL <Parameters> Aggr(If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), Parameters, ID)) > If(Len(Trim([Date Closed])) > 0, ([Date Closed] - [Date Open]), (Max(TOTAL <ID> [Date Updated]) - [Date Open])), ID) , ID, Parameters))
Thanks Sunny