New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More
New Contributor

Count Ids above Average

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

1 Solution

Accepted Solutions MVP

Re: Count Ids above Average

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))
4 Replies MVP

Re: Count Ids above Average

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))
New Contributor

Re: Count Ids above Average

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). MVP

Re: Count Ids above Average

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))
New Contributor

Thanks  Sunny