topic Re: Year to Date Average Set Analysis in App Development

Year to Date Average Set Analysis

ZoeM — Thu, 11 Apr 2024 14:58:03 GMT

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.

Re: Year to Date Average Set Analysis

mpc — Thu, 11 Apr 2024 16:30:29 GMT

Hi,

Maybe this one ?

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

I haven't tested it !

Regards

Re: Year to Date Average Set Analysis

ZoeM — Thu, 11 Apr 2024 16:51:52 GMT

Thanks @mpc

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

Re: Year to Date Average Set Analysis

mpc — Fri, 12 Apr 2024 07:10:18 GMT

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

Re: Year to Date Average Set Analysis

mpc — Fri, 12 Apr 2024 11:35:27 GMT

Maybe this one:

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

Re: Year to Date Average Set Analysis

ZoeM — Tue, 16 Apr 2024 21:38:50 GMT

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.