0

Debt and credit

I have 3 columns for debater, creditor and balance. What formula do i need to show the balance columns. So the balance columns it is a difference of the debtor and creditor based on the picture attached

15 replies

null
    • Nick
    • 5 yrs ago
    • Reported - view

    Running Balance?

    I'm interest in this, too.

    • Support
    • 5 yrs ago
    • Reported - view

    Hi, 

    You can use a formula like the following, if you use the ID field as order field (you could possibly rather use a date+time field for this).

     

    let myID := _id;
    let before_d := sum((select myTable).Debtor);
    let before_c := sum((select myTable).Credit);
    before_d - before_c

     

    Best, Jörg

    • saeedr50
    • 5 yrs ago
    • Reported - view

    Dont worked. Result in columns of balance are same . Columns of balance is formula and wrote there. 

    • saeedr50
    • 5 yrs ago
    • Reported - view

    I need be in picture of below:

    • Support
    • 5 yrs ago
    • Reported - view

    Hi, 

    Sorry, I missed the condition. Please try this formula:

     

    let myID := _id;
    let before_d := sum((select myTable)[Id <= myID].Debtor);
    let before_c := sum((select myTable)[Id <= myID].Credit);
    before_d - before_c

    Best, Jörg

    • saeedr50
    • 5 yrs ago
    • Reported - view

    Great!  Worked.

    this formula is important for use in accounting.

    please let me know , how can use continue formula that when balance is negative ,then show me red number

    thank you.

    • Jorg
    • 5 yrs ago
    • Reported - view

    Hi, 

    You can use the styled() function for that.

     

    let myID := _id;
    let before_d := sum((select myTable)[Id <= myID].Debtor);
    let before_c := sum((select myTable)[Id <= myID].Credit);
    let result := before_d - before_c;
    styled(text(result), if text(result) < 0 then "red" else "green" end, "")

    Best, Jörg

    • New_Sun
    • 5 yrs ago
    • Reported - view

    but <0 and >0 are green,  <0 dont want be red and result for both are green color.

    • saeedr50
    • 5 yrs ago
    • Reported - view
    • New_Sun
    • 5 yrs ago
    • Reported - view

    worked, thank you.

    • saeedr50
    • 5 yrs ago
    • Reported - view

    Result is incorrect when i add another number. I think dont work on large number. In picture below you can see everything is ok but when i add another number in credit or debt ,then result is incoorect

    • saeedr50
    • 5 yrs ago
    • Reported - view

    In picture in below you can see i add in credit 500.000.000 but result is mistake

    • Jorg
    • 5 yrs ago
    • Reported - view

    Hi, 

    That looks strange ideed. Could you please send us copy of that database to support@ninoxdb.de (please delete all sensitve data before and only keep the number data that we need can see that issue)?

    Thank you very much. Best, Jörg

    • Jorg
    • 5 yrs ago
    • Reported - view

    Hi, 

    I checked it now with your example and I realised the it was because of using the Ninox Id to order the records. As they are not stored in a number field but in a text field the order will fail the expected result starting with the 10th row as it will be ordered alphanumerically.

    To avoid this please use the formula as follows: 

     

    let myID := number(_id);
    let before_d := sum((select myTable)[number(Id) <= myID].Debtor);
    let before_c := sum((select myTable)[number(Id) <= myID].Credit);
    let result := before_d - before_c;
    styled(text(result), if text(result) < 0 then "red" else "green" end, "")

     

    Best, Jörg

    • New_Sun
    • 5 yrs ago
    • Reported - view

    Dear Jorg;

    thank you, it is worked and everything is ok.

Login to reply

Content aside

  • 5 yrs agoLast active
  • 15Replies
  • 3348Views