topic Re: Set Analysis Accumulation Not Working in App Development

Set Analysis Accumulation Not Working

asmithids — Sat, 16 Nov 2024 06:17:25 GMT

Hello,

I have the following "As of" expression that is not showing the accumulation of the payment amount after the period when the payment first happened in 2012-Dec. The set analysis syntax has a search parameter to include all payments on or before the Max([Year Month]). In this case the Max([Year Month]) is 2018-May.

Expression: Sum({<Year=,Month=,[Year Month]={"<= $(=Max([Year Month]))"}>} PaymentAmount)

See attached screen shot of a table showing the payments. The periods following 2012-Dec are zero, but should be 1,891.87.

Any information to resolve this issue would be greatly appreciated!

Thank you,

Re: Set Analysis Accumulation Not Working

Lisa_P — Thu, 21 Mar 2019 02:28:09 GMT

The Max aggregation is for numeric fields, the Year month is a text string, so max will not work here

Re: Set Analysis Accumulation Not Working

tresB — Thu, 21 Mar 2019 07:51:34 GMT

It's better to deal with the string date fields in the script. However, if that is not in the scope, you could try parsing the string in set analysis itself like:

Sum({<Year=,Month=,[Year Month]={"=Date#([Year Month], 'MMM-YYYY') <=
 $(=Max(Date#([Year Month],'MMM-YYYY')))"}>} PaymentAmount)

Re: Set Analysis Accumulation Not Working

asmithids — Fri, 22 Mar 2019 15:53:36 GMT

Hi Lisa_P,

Thank you for the help!

The expression I am using is numeric. However, to confirm if the "Year Month" was the issue, I changed the expression to the following. The change did not correct the issue. Please see attached screen shot of the table using the new expression.

Expression: Sum({<Year=,Month=,[_MonthSerial]={"<= $(=Max([_MonthSerial]))"}>} PaymentAmount)

I would have thought the "< =" syntax in the expression would be able to show the balance forward to all future periods after the initial posting in Dec-2012.

Re: Set Analysis Accumulation Not Working

sunny_talwar — Fri, 22 Mar 2019 18:15:26 GMT

Are you looking for this may be

RangeSum(Above(
Sum({<Year=,Month=,[Year Month]={"<=$(=Max([Year Month]))"}>} PaymentAmount)
, 0, RowNo()))

Re: Set Analysis Accumulation Not Working

asmithids — Tue, 09 Apr 2019 22:51:38 GMT

Thanks Sunny!!