Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
could you please specify your input data?
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
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
Try with this script
Sum(
{<YourDatefield={">=$(= Min(Date(YourDatefield, 'MM/DD/YYYY'))) <=$(= Max(Date(v_end_date, 'MM/DD/YYYY'))-1)"}
>}
SumValue)
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.