Sum up to a date using Set Analysis in a Chart Table - need help please!

[#1]

Hi,

I have a table of credit and debit movements for monthly periods and I need a balance column. 

I tried originally with an If statement which works find to give the total movements within that period, but because the EndOfPeriod is a dimension in the table, this will not show an accumulated balance over a whole year. 

Sum(if(TransactionDate <= EndOfPeriod,BaseCredit,0)) - Sum(if(TransactionDate <= EndOfPeriod,BaseDebit,0))

I'm pretty sure Set Analysis should be able to answer my questions but I can't figure out how to make it work.  I've tried the below (and a few variations on a theme) but have come up with nothing that gives me what I need.

Sum({<TransactionDate = {"<=EndOfPeriod"}>} BaseCredit)-Sum({<TransactionDate = {"<=EndOfPeriod"}>} BaseDebit)

Please can anyone help?

Many thanks,

Emma

[kaushiknsolanki]

Hi,

     Why dont you create a field in your script which is nothing but the Cr-Dr.

     Then you can just sum up to get the balance as of date.

Regards,

Kaushik Solanki

[#3]

Hi Kaushik,

Not really sure I understand how that would work.

My original data looks something like this:

Account     TransactionDate     EndOfPeriod     BaseCredit     BaseDebit

ex123          03-04-2012          30-04-2012          0                         150

ex123          06-04-2012          30-04-2012          2500                    0

as456          21-04-2012          30-04-2012          250                      0

But with about 50 account codes and transaction dates ranging from March 2010 to April 2012.  I need to show an end of period balance, for each period and for each account code, that is a sum of credits-debits from March 2010 until the end of period date. 

I can get a total balance using ALL or I can get an end of period balance that just calculates movement within a period, what I am missing is the end of period balance that goes from March 2010 to the end of period.

Thanks,

Emma

[kaushiknsolanki]

Hi,

     Emma, tell me one thing.

     Do you mean you want to show the chart which will have month as dimension and the balance as of last day of that month?

Regards,

Kaushik Solanki         

[Gysbert_Wassenaar]

If you're using EndOfPeriod as dimension in your chart then read this document: Calculating rolling n-period totals, averages or other aggregations

[#6]

Hi Gysbert,

Really helpful doc - now if I can figure out how to make it work over 25 months (the time period that my data covers) and aggregate on account code as well as month that would be ideal.

Gradually getting there but getting some funny answers when I add the account code into the aggregation.

Thanks,

Emma

[#7]

Hi Kaushik,

Yes - I need to see both the month dimension and the balance as of the last day of the month - but it is an opening date (March-2010) up to the month balance which is causing the problems. 

Thanks,

Emma