Year to Date Average Set Analysis

[ZoeM]

Hello.

I am trying to calculate the year to date average based on year selected. I do have a Master Calendar with a YearMonth field.

I would like to build a Set Analysis expression so that when certain criteria are fulfilled it calculates my average.

Below is a sample of data where the average value should fall in the Completion Month. As shown in the data, January value will be zero, anyway to force the zero in the chart when there is no value as well (null).

Criteria for the Set analysis expression is:

Products (All)

Product Type (Mens, Women)

YTD Average of days based on End Date.

[mpc]

Hi, 

Maybe this one ?

Avg({<Product=,[Product Type]={'Mens,Womens'},[Start Date]={">= $(=YearStart(Today()))"}, [End Date]={"<= $(=Today())"}>} Days) 

I haven't tested it !

Regards

[ZoeM]

Thanks @mpc 

However it did not change the data/work as expected. 

[mpc]

So I need to test it. I'll be back (Terminator voice) 

[mpc]

Maybe this one: 

Avg({< Product=,[Product Type]={'Mens','Womens'},YearMonth=,[Start Date]={">=$(=YearStart(YearMonth))"},[End Date]={"<=$(=(Today()))"} >} Days)

[ZoeM · Accepted solution]

So I was able to find a solution that fits my needs by using the Range expression combined with Set analysis.

Below is my expression, make sure you do not select accumulate since the expression itself will do that for you. 

RangeSum(above((sum({$<[Date Type]={'Sale Date'},Products={'Shoes','Shirts','Socks'},Sale={'Y'}>} [Business Days Late])), 0, rowno()))
/
RangeSum(above((Count({$<[Date Type]={'Sale Date'},Gateways={'Shoes','Shirts','Socks'},Sale={'Y'}>} [product line])), 0, rowno()))

I hope this helps someone else. And thanks for everyone who helped me come to this solution.