Qlik Sense Set Analysis Expression Previous Period Sum

[rnguyen2]

Hello,

I need some help with my set analysis expression.

I am trying to populate the beginning AR Balance for the month with the ending AR Balance for the prior month.

Below is my set expression.

Sum({$<[posting_date.autoCalendar.Date]={"$=addmonths([posting_date.autoCalendar.Date],-1)"}, BALANCE_TYPE={"DEBIT_BALANCE"}>} [AR BALANCE])

[rogerpegler]

Assuming everything else is OK, you may be missing brackets:

Sum({$<[posting_date.autoCalendar.Date]={"$(=addmonths([posting_date.autoCalendar.Date],-1))"}, BALANCE_TYPE={"DEBIT_BALANCE"}>} [AR BALANCE])

[rnguyen2]

This did not work either

[rnguyen2]

Here a mock up I put together and attached

[rogerpegler]

Having seen the app - @sunny_talwar 's solution of using the Before function should work.  Note it only works in Pivot tables.

[sunny_talwar]

I used this

Alt(Before(Sum({<BALANCE_TYPE={"DEBIT_BALANCE"}>} [AR BALANCE])), 0)

to get this

Is this not right?

[rnguyen2]

I could use that function but it shows the incorrect value if I filter certain YearMonths.

In the example attached. I need it to keep 2019-Feb Ending Gross AR Balance for 2019-Mar Beginning Gross AR Balance.

[sunny_talwar]

For that you can try this

Alt(Before(Sum({<BALANCE_TYPE = {"DEBIT_BALANCE"}, [posting_date.autoCalendar.YearMonth]>} [AR BALANCE])), 0) * Avg(1)