Announcements
cancel
Showing results for
Did you mean:
Specialist

## Year to Date Average Set Analysis

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.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Specialist
Author

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.

5 Replies
Partner - Specialist

Hi,

Maybe this one ?

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

I haven't tested it !

Regards

From Next Decision with love
Helpful, like it, solved, mark it
Specialist
Author

Thanks @mpc

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

Partner - Specialist

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

From Next Decision with love
Helpful, like it, solved, mark it
Partner - Specialist

Maybe this one:

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

From Next Decision with love
Helpful, like it, solved, mark it
Specialist
Author

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.