Set Analysis Expression For Balance Sheet Accounts

[asmithids]

Hello,

I have the following expression for YTD sum.  The issue is that I need to sum account transactions from the beginning of time to the current Year-Month selection for a Balance Sheet report.  Balance Sheets are an accumulating financial report that maintains a running total from the beginning of time.  I'm not sure how to alter the expression below to start the summation from the beginning of time (i.e. 2007) up to and including the Year-Month selection.  

Thank you in advance for assistance with this question.   

Sum({$<Year={$(=Max(Year))},Month={"<=$(=Max(Month))"}>}TransactionAmount))

[swuehl · Accepted solution]

I think it would be easier to create a field containg the Year-Month, i.e. the Monthstart date showing unique values for every combination of Year and Month (I assume Year and Month fields have a numeric representation in your data model):

In your load script, something like this:

LOAD

     Year,

     Month,

     Makedate(Year, Month) as MonthStart

FROM ...

Then:

Sum({$<Year=, Month=, MonthStart = {"<=$(=Max(MonthStart))"}>} TransactionAmount))

[Anonymous]

My 2 cents...  actually only 1 cent:

Set analysis is "format-sensitive".  In the example from Stefan the MonthStart field is in the date format, but max(MonthStart) will be integer.  To make it work, a small change is needed:

Sum({$<Year=, Month=, MonthStart = {"<=$(=date(Max(MonthStart)))"}>} TransactionAmount))

[asmithids]

Thank you swuehl.  That did the trick!!! 

[asmithids]

Hi Michael,

I made the change.  Thank you!