Running total across dimensions

[RedSky001]

I have Loan of 1,000.

In 2012, Q1 the interest due is 100

In 2012, Q4 the interest due is 90

In 2013, Q1 the interest due is 80

In 2013, Q4 the interest due is 70

I want to see the balance at any given year or month that I can see at year or at month.

I've created a pivot table with Year & QTR as my horizontally alligned dimensions.

With the following Expression [Bal]

if(YEAR=2012 and QTR=1,vLoanAmt - sum(INTEREST) // Start of Loan repayments

,Rangesum(-sum(INTEREST),before([Bal])))

The problem is the before() function works with the ColumnNo(), which resets to 1 for 2013 (I want it to be 3).

(Example attached)

Any ideas?

** EDIT

Found an expression which seems to do the trick:

if(YEAR=2012 and QTR=1,vLoanAmt - sum(INTEREST) // Start of Loan repayments
,vLoanAmt - Rangesum(before(Total  sum([INTEREST]),0,ColumnNo(TOTAL))))

Mark

[jagan]

Hi,

Use the below expression in your chart

if(YEAR=2012 and QTR=1,vLoanAmt - sum(INTEREST) // Start of Loan repayments

,Rangesum(-sum(INTEREST),before(TOTAL [Bal])))

Hope this helps you.

Regards,

Jagan.

[jagan]

Hi,

Use below expression.

if(YEAR=2012 and QTR=1,vLoanAmt - sum(INTEREST) // Start of Loan repayments

,Rangesum(-sum(INTEREST),before(TOTAL [Bal])))

Regards,

Jagan.

[jagan]

Hi,

Check this expression

if(YEAR=2012 and QTR=1,vLoanAmt - sum(INTEREST) // Start of Loan repayments

,Rangesum(-sum(INTEREST),before(TOTAL [Bal])))

regards,

jagan.

[RedSky001]

Still not quite right.

let vLoanAmt = 1000;

LOAD * INLINE [

          YEAR,QTR,INTEREST

          2012,1,100

          2012,4,100

          2013,1,100

          2013,4,100

];

I have the following expression to accumulate the interest over the dimensions.

Rangesum(before(Total  sum([INTEREST]),0,ColumnNo(TOTAL)))

This works exect when I collapise the year 2013.  I'd expect to see 400 but see 0.

This is because there is nothing "Before" to summarise until I collapose 2012. 

Any suggestions?