Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
Specialist

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
sunny_talwar

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
Specialist
Specialist
Author

Dear Members,Look forward for help

 

Thanks

Neville

sunny_talwar

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
Specialist
Specialist
Author

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

 

sunny_talwar

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

image.png

nevilledhamsiri
Specialist
Specialist
Author

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

 

sunny_talwar

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)

 

nevilledhamsiri
Specialist
Specialist
Author

 

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

 

sunny_talwar

Because you have not updated the expression for BF yet 🙂

nevilledhamsiri
Specialist
Specialist
Author

Tanks Sunny

 

Neville