Charting balances for every month up to month selected

[#1]

I have a "Beginning of year balance" for each account. I have "Debits" and "Credit" for each month. When I select "Jan", my balance for the month is correct as it sums the "Beginning of the year balance" with my "Debits" and "Credits". When I select "Feb", my "Feb" balance is correct however my "Jan" balance changes to the sum of the "Debits" and "Credits" for the month of "Jan". When I select "Mar", the "Mar" balance is correct however, now "Jan" and "Fab" balances change as stated above. This process repeats itself as I make selections further into the year. I am only working with one year of data.

My goal is for the chart to accurately display the balance at the end of each month as a YTD total through the different months.

Here is my script:

If(isNull([Beginning of Year Balance]),0,[Beginning of Year Balance] )+

sum({$<Year = {2010}, Month = {"<=$(=max(Month))"}>} JournalCredit )-

sum({$<Year = {2010}, Month = {"<=$(=max(Month))"}>} JournalDebit )

I am "accumulating" balances.

[#2]

Hi,

take care of the if() at the beginning of your expression. It isn't an aggregate-function. So it works fine for the selected month(s). This is to say when you select one month then all the excluded months will have a null-value for "Beginning of year balance". You can easily proof this by creating an expression with only "If(isNull([Beginning of Year Balance]),0,[Beginning of Year Balance] )". Then select exactly one month and after that select all months from start of the year up to your prior selected month. Now the if() can (will) work as you expect it.

The sum() is working excatly as you want because you use an aggregate function (the sum()) with the SET Analysis to sum up all months till max(month). With this in mind I can offer you as a workaround the avg(). I assume that your beginning of the Year is constant for every month. Then try the following instead of the if():

avg({ < ... Month = {"<=$(=max(Month))"}>} Beginning.....)

Note: If you can (and want to) change your load-script, I would think about changing the value for the "BeginYear"-Field and load this value only when month=Jan, otherwise "0". Not only that this is more correct then having every month a start-value, but then you can very easy use sum()-functions like

sum({$<Year = {2010}, Month = {"<=$(=max(Month))"}>} Beginning + JournalCredit - JournalDebit )

Regards, Roland

[#3]

Thanks Roland. Here is what I did:

sum

({$<Year = {2010}, Month = {"<=$(=max(Month))"}>} JournalCredit - JournalDebit )

+

if(Month='Jan', (if(isNull([Beginning of Year Balance]),0,[Beginning of Year Balance])),0)

If I select "Apr" as an example, the balances for each from "Jan-Apr" are not correct. If I select all the months from "Jan-Apr", all my balances are correct. Do I need to add more script or correct what I have?

[#4]

Hi,

ddebonis wrote: If I select all the months from "Jan-Apr", all my balances are correct.

As I mentioned above the if() works fine for the selected month(s). In consequence the user has to choose the correct time-period (like you did above). For a possible workaround please see my example app. Comments on it you can find my prev post.

RR