topic Re: Calculate opening balance in QlikView

Calculate opening balance

arulsettu — Sun, 22 Mar 2015 08:23:52 GMT

Hi,

I am trying to show opening balance. i am having month field

TBAL_CAL_MONTH

if i select 7th month it should show the value of 0-6 th month as opening balance

i tried below code but no luck

sum({<TBAL_CAL_MONTH={">=$(=MonthsStart(Date(Max(TBAL_CAL_MONTH))))<=$(=Max(Date(TBAL_CAL_MONTH)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

plz suggest

thanks

Re: Calculate opening balance

arulsettu — Sun, 22 Mar 2015 08:58:33 GMT

is this Rolling month?

plz suggest some advice

Re: Calculate opening balance

arulsettu — Sun, 22 Mar 2015 09:57:07 GMT

any solutions?

Re: Calculate opening balance

preminqlik — Sun, 22 Mar 2015 10:10:18 GMT

hi der

try this

sum({<TBAL_CAL_MONTH={">=$(=min({1}TBAL_CAL_MONTH))<$(=max(TBAL_CAL_MONTH))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

Re: Calculate opening balance

arulsettu — Sun, 22 Mar 2015 10:15:25 GMT

thanks for replying but it showing zeros only

Re: Calculate opening balance

Sun, 22 Mar 2015 10:38:50 GMT

try this:

sum({<TBAL_CAL_MONTH={"<$(=Max(TBAL_CAL_MONTH))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

is there a year field?

Re: Calculate opening balance

preminqlik — Sun, 22 Mar 2015 10:46:41 GMT

post sample data

Re: Calculate opening balance

simsondevadoss — Sun, 22 Mar 2015 11:03:27 GMT

RangeSum(Above(sum( {<TBAL_CAL_MONTH={"<$(=Max(TBAL_CAL_MONTH))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1),0,RowNo()))

Re: Calculate opening balance

arulsettu — Sun, 22 Mar 2015 11:06:39 GMT

here is the sample app

Re: Calculate opening balance

arulsettu — Sun, 22 Mar 2015 11:13:18 GMT

Hi Simson,

Thanks for reply but the result is not correct

Re: Calculate opening balance

simsondevadoss — Sun, 22 Mar 2015 11:19:52 GMT

Use this :

sum({<TBAL_Month={'<=$(vTBL_month)'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

Re: Calculate opening balance

simsondevadoss — Sun, 22 Mar 2015 11:21:39 GMT

Also change the variable to vTBL_month=max(TBAL_Month)-1

Re: Calculate opening balance

arulsettu — Sun, 22 Mar 2015 11:37:07 GMT

please check this app i selected some fields.the results are not correct

expected is

Cash and cash equivalents

1.7157E+09

Assets

Insurance and other receivables

-8.618E+08

Assets

Investments

4.5878E+09

Assets

Property and equipment

20462764.6

thanks

Re: Calculate opening balance

arulsettu — Sun, 22 Mar 2015 11:37:48 GMT

check

Re: Calculate opening balance

arulsettu — Mon, 23 Mar 2015 03:34:54 GMT

does any one have idea about this its emergency plz suggest

Re: Calculate opening balance

sagarkharpude — Mon, 23 Mar 2015 04:38:42 GMT

try this

sum({<TBAL_Month={'<$(=Max(TBAL_Month)-1)'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

Re: Calculate opening balance

arulsettu — Mon, 23 Mar 2015 05:00:56 GMT

Actually what i need is if i select jun month it should give calculate may,apr,mar,feb,jan values as opening balance of june

this is the expected output

Cash and cash equivalents

1.7157E+09

Assets

Insurance and other receivables

-8.618E+08

Assets

Investments

4.5878E+09

Assets

Property and equipment

20462764.6

if i select these filters i should get above result

TBAL_CAL_YEAR=2014

AND TBAL_COMP_CODE='001'

tbal_month= jun

thanks

Re: Calculate opening balance

arulsettu — Mon, 23 Mar 2015 05:21:54 GMT

this is the expression i used

=Sum( {$<TBAL_Month={">=$(=MonthStart(AddMonths(Max(TBAL_Month),-13)))<$(=MonthEnd(Max(TBAL_Month)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)+sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1)-sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_CR_LC_1)

Re: Calculate opening balance

Mon, 23 Mar 2015 05:53:32 GMT

Your

=sum(TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

exp returns 1647111788.9404 & selection of 0-6 month opening balance is -65453344 for same exp

your expectation is 1715700000 with filtered data is your measure calculation right ?

Re: Calculate opening balance

arulsettu — Mon, 23 Mar 2015 06:05:46 GMT

=sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

above is current balance

to find opening balance i have to calculate previous months balance and add to current balance

this 1715700000 output is current balance + previous months balance

if i select these filters

TBAL_CAL_YEAR=2014

AND TBAL_COMP_CODE='001'

tbal_month= jun

i should get this 1715700000 result for cahs and cash equivalents