topic Re: Set Analysis Formula not working in App Development

Set Analysis Formula not working

bates3jr — Thu, 30 Jun 2022 14:30:24 GMT

Hello all,

The following formula is not working and I'm not really sure why:

sum({$<[Effective Date]={'=[NextMonth]'}>} [Forecasted_Demand] )

I have a pivot table and in the columns I see months. The formula is intended to be a measure for the next months forecasted demand. Example, in the April column, I want to see May demand etc. My effective date field is correct when I add it to a table and my "NextMonth" field is correct when I add it to a table. But for some reason, the above formula is showing the current months Forecasted Demand rather than the "NextMonth".

The date values are in 'YYYY-MM-DD' format. I've attached an example that shows what my pivot table looks like. The "Next Month Demand" should equal the following months "Forecast Demand".

Any ideas how to fix this?

Re: Set Analysis Formula not working

bates3jr — Thu, 30 Jun 2022 14:58:32 GMT

Found a formula similar to this that appears to be bringing in the correct total:

sum( aggr( rangesum( below( sum(Forecasted_Demand))),[NextMonth]))

However, when I add dimensions to the rows in my pivot table, the total is not working properly.

Re: Set Analysis Formula not working

bates3jr — Wed, 06 Jul 2022 13:59:33 GMT

Anyone know how to get this formula to work across multiple dimensions?