topic Running total across dimensions in QlikView

Running total across dimensions

RedSky001 — Sun, 26 Feb 2012 15:49:36 GMT

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

Running total across dimensions

jagan — Mon, 27 Feb 2012 05:53:45 GMT

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.

Running total across dimensions

jagan — Mon, 27 Feb 2012 05:54:26 GMT

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.

Running total across dimensions

jagan — Mon, 27 Feb 2012 05:55:40 GMT

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.

Running total across dimensions

RedSky001 — Mon, 27 Feb 2012 10:35:14 GMT

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?