Count with Average (Aggr function)

[SkitzN27]

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!

[GaryGiles]

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]

Hey, it doesn't work.

@GaryGiles 

[GaryGiles]

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 · Accepted solution]

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