Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SkitzN27
Creator
Creator

Count with Average (Aggr function)

Hi guys,

I want to use count and avg together but can't get it to work.

I have to count the studies when all these conditions are met.

Can anyone help?

count( aggr
if([Dimension1]='abc' and [Dimension2]='Closed' and Yearstart([Datefield]=yearstart(today()))
and avg([No. of Days])<=7,1,0) , StudyNumber))

 

I believe I'll have to use Aggr but don't know how.

 

Thanks so much!

1 Solution

Accepted Solutions
Kushal_Chawda

@SkitzN27  try below. Make sure that the Date field is in proper date format

=sum({<[Dimension1]={'abc'},[Dimension2]={'Closed'},[Datefield]={"$(=date(yearstart(today())))"}>} aggr(
if(avg({<[Dimension1]={'abc'},[Dimension2]={'Closed'},[Datefield]={"$(=date(yearstart(today())))"}>}[No. of Days])<=7,1,0) , StudyNumber))

 

View solution in original post

4 Replies
GaryGiles
Specialist
Specialist

Try this:

Count(if(aggr(avg({$<[Dimension1]={'abc'},[Dimension2]={'Closed'},[Datefield]={">=$(=YearStart(Today()))<=$(=Today())"}>} [No. of Days])<=7, StudyNumber))

You may want to add a distinct if there can be multiple records for a StudyNumber.

SkitzN27
Creator
Creator
Author

Hey, it doesn't work.

@GaryGiles 

GaryGiles
Specialist
Specialist

Sorry, I don't have your exact fields to test it, so the syntax was a little off:

Try this:

Count(if(aggr(avg({$<[Dimension1]={'abc'},[Dimension2]={'Closed'},[Datefield]={">=$(=YearStart(Today()))<=$(=Today())"}>} [No. of Days]), StudyNumber)<=7, StudyNumber))

Kushal_Chawda

@SkitzN27  try below. Make sure that the Date field is in proper date format

=sum({<[Dimension1]={'abc'},[Dimension2]={'Closed'},[Datefield]={"$(=date(yearstart(today())))"}>} aggr(
if(avg({<[Dimension1]={'abc'},[Dimension2]={'Closed'},[Datefield]={"$(=date(yearstart(today())))"}>}[No. of Days])<=7,1,0) , StudyNumber))