Qlik Community

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
Wilbert
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

 

ParametersIDDate OpenDate ClosedDate Updated
TemperatureAB5/13/201712/16/201712/18/2017
Wind forceAD6/8/2018 10/10/2018
Wind forceAD6/8/2018 11/10/2018
HumidityAC3/10/20181/2/20192/7/2019
TemperatureAG8/15/2018 11/20/2018
HumidityAH5/3/2018 8/19/2018
HumidityAH5/3/2018 9/2/2018
Wind forceAO7/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:

CasesAverage TimeNo of IDs Above Average
Temperature1571
Humidity210.51
Wind force811

 

 

 

 

 

 

1 Solution

Accepted Solutions

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

View solution in original post

4 Replies

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

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

View solution in original post

Wilbert
New Contributor

Re: Count Ids above Average

Thanks  Sunny