# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
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