Set Analysis and max date

[Anonymous]

Hello,  I'm trying to compare amounts based on the max month available.

My issue occurs whenever I select a month, the expressions sum the selected month's amount instead of the previous month's amount.

Here's an example of my code

First Column:

=sum({<Reporting_Period_Num = {'=(max(Reporting_Period_Num))'}>} Account_Balance)

Second column:

=sum({<Reporting_Period_Num = {'=(max(Reporting_Period_Num)-1)'}>} Account_Balance)

[sunny_talwar]

You mean your second column shows you the sum for the selected period?

[Anonymous]

Both columns do.  When a month is not selected, it shows the sum for all periods as if the set analysis is being ignored

[sunny_talwar]

See if this helps:

First Column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num))"}>} Account_Balance)

Second column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}>} Account_Balance)

[Anonymous]

That just gives me 0's across the board

[sunny_talwar]

I made some changes to the expression, did you use these?

First Column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num))"}>} Account_Balance)

Second column:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}>} Account_Balance)

If not, try them. If you did, then can you share some date to check?

[Anonymous]

Close.  It works now until I select a month, then the column that takes the max period-1 turns to 0

[sunny_talwar · Accepted solution]

I think you will need to add other mastercalendar fields in here to work:

=Sum({<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}, Month = , Year = , Date = >} Account_Balance)

or try this with a 1 to restrict any selections except the one (Reporting_Period_Num)

=Sum({1<Reporting_Period_Num = {"$(=Max(Reporting_Period_Num)-1)"}>} Account_Balance)

[Anonymous]

That works!  The 1 in front is making the difference.  Thank you so much!!

[sunny_talwar]

But make sure you understand what it is doing. 1 in the front will make the expression not respond to any selection except Reporting_Period_Num. Is that what you want? If not, I would suggest using the other expression with all the master calendar fields in it.

Best,

Sunny