Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
nevilledhamsiri
Valued Contributor

C/F Becoming B/F

I always need the calculated closing balance to be appeared as opening balances in the next Month. As per my calculation, figures are ok but it is appearing after C/F balance but I need to make it appear in the below row as b/f in February. Can some one help me to achieve this?

 

Thanks

 

Neville

1 Solution

Accepted Solutions

Re: C/F Becoming B/F

Expression for BF

Sum(Aggr(If(RowNo() = 1, Sum([B/F]), Above(SUM([B/F])+SUM(CONTRIBUTION)-SUM(SETTLEMENT))), BR_CODE, DES, DATE))

Expression for CF

Sum(Aggr(If(RowNo() = 1, Sum([B/F]), Above(SUM([B/F])+SUM(CONTRIBUTION)-SUM(SETTLEMENT))), BR_CODE, DES, DATE))+SUM(CONTRIBUTION)-SUM(SETTLEMENT)

 

View solution in original post

9 Replies
nevilledhamsiri
Valued Contributor

Re: C/F Becoming B/F

Dear Members,Look forward for help

 

Thanks

Neville

Re: C/F Becoming B/F

Is this what you are looking to get to?

image.png

Try this

Aggr(
  Above(
    Sum([B/F]) + Sum(CONTRIBUTION) - Sum(SETTLEMENT)
  )
, BR_CODE, DES, DATE)

 

nevilledhamsiri
Valued Contributor

Re: C/F Becoming B/F

Actually, what is ended as C/F in January (10000,2500,12500) needs to be brought forward in February. In other words  in February it should be shown as below

 

 

BR_CODEDATEDESB/FCONTRIBUTIONSETTLEMENTC/F
RA1/1/2019SAL_ADVANE5000250050002500
RA1/1/2019TRAVELLING100005000250012500
RA1/1/2019ADVANCE250010000250010000
RA2/1/2019SAL_ADVANE2500500010006500
RA2/1/2019TRAVELLING125005000100016500
RA2/1/2019ADVANCE100005000100014000

 

Re: C/F Becoming B/F

This seems to work for 2 dates, but might have to be tuned for multiple dates...

image.png

nevilledhamsiri
Valued Contributor

Re: C/F Becoming B/F

Thanks sunny for reply. Could you please forward me the expression used to fix the data as in the table you forwarded. I cannot open up your qvd. The formula you sent earlier, didn't help me to arrange the b/f  as I need.Please send me the expression used.

 

Thanks

Neville

 

Re: C/F Becoming B/F

Expression for BF

Sum(Aggr(If(RowNo() = 1, Sum([B/F]), Above(SUM([B/F])+SUM(CONTRIBUTION)-SUM(SETTLEMENT))), BR_CODE, DES, DATE))

Expression for CF

Sum(Aggr(If(RowNo() = 1, Sum([B/F]), Above(SUM([B/F])+SUM(CONTRIBUTION)-SUM(SETTLEMENT))), BR_CODE, DES, DATE))+SUM(CONTRIBUTION)-SUM(SETTLEMENT)

 

View solution in original post

nevilledhamsiri
Valued Contributor

Re: C/F Becoming B/F

 

Dear Sunny,

Thanks for the expression. Why the c/f  is not coming to appear in the B/F in February. Could you please see why the closing balance in January (c/f) is no coming to appear in opening balance(b/f) in February.

 

Thanks

 

Re: C/F Becoming B/F

Because you have not updated the expression for BF yet 🙂

nevilledhamsiri
Valued Contributor

Re: C/F Becoming B/F

Tanks Sunny

 

Neville