topic Re: Beginning Balance in QlikView

Beginning Balance

Mon, 14 Apr 2014 19:48:20 GMT

Good day, I find myself making a financial model in Qlikview and I calculate in a pivot table the "beginning balance", which is basically the sum of all historical data loaded to the selected date.

please can you help me with the expression for this calculation?. Thank you very much.

Re: Beginning Balance

MarcoWedel — Mon, 14 Apr 2014 20:28:17 GMT

could you please specify your input data?

Re: Beginning Balance

Mon, 14 Apr 2014 20:53:52 GMT

The table will be something like this. For example, if you select February 2014, the beginning balance is the sum of all data in the system until January 2014.

Thank you so much for your help

Re: Beginning Balance

Mon, 14 Apr 2014 21:06:10 GMT

the expression could look something like:

sum(

[Date Field in your records]={">=$(=date(v_start_date, 'MM/DD/YYYY'))<=$(=date(v_end_date, 'MM/DD/YYYY'))"}

[Field name storing the amounts])

v_start_date = earliest date you want

v_end_date = latest date you want

Re: Beginning Balance

its_anandrjs — Mon, 14 Apr 2014 21:16:32 GMT

Try with this script

Sum(

{<YourDatefield={">=$(= Min(Date(YourDatefield, 'MM/DD/YYYY'))) <=$(= Max(Date(v_end_date, 'MM/DD/YYYY'))-1)"}

SumValue)

Re: Beginning Balance

Colin-Albert — Mon, 14 Apr 2014 21:53:42 GMT

This post may be useful. It uses a straight table rather than a pivot table which gives the options to add blank rows in your financial statement.

How to Create a Profit and Loss Statement in QlikView