6 Replies Latest reply: Jul 15, 2013 6:07 AM by Emma Self RSS

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



      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,