topic Re: Find an opening balance in QlikView

Find an opening balance

Wed, 06 Nov 2013 15:26:58 GMT

I need to sum all transactions which took place prior to the current year in the starting balance column of the attached example application but only If the project has a transaction amount in the current year. If no transaction is present in the current year then the starting balance should be 0. The field should be dynamic so if a previous year is selected the same rules will follow.

Thank you for your help

Re: Find an opening balance

Gysbert_Wassenaar — Wed, 06 Nov 2013 19:06:46 GMT

See attached qvw

Re: Find an opening balance

Thu, 07 Nov 2013 02:25:49 GMT

Thank you! What a genius solution.

I had to alter the expression ever so slightly to get exactly what was required

altered from:

( {< ProjectCode= p( {<TransactionYear={$(=max(TransactionYear))}>} ),TransactionYear={$(=max(TransactionYear)-1)}>}TransactionAmount)

To:

( {< ProjectCode= p( {<TransactionYear={$(=max(TransactionYear))}>} ),TransactionYear={ "< $(=max(TransactionYear)-1)" }>}TransactionAmount)