0

Sum a customer sale in a view

I would like to sum all Sales for a customer in a view table shown in a formula field. 
l have 

let xBeg:= 'Beg Date'

let xEnd:= 'End Date'

sum((select Invoices) ['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd].........
Not sure what comes after that.

l have tried Customer.133    Being the Ninox Id but does not return correct balance  

l have linked the Customer table  but have no reference to it in the above formula.

Regards 

Michael

3 replies

null
    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Michael

     

    I can put something together later on today.

    From your Customer table, can you let me have the name of the relationship to your Invoices table (it might also be called Invoices).

    From your Invoices table, can you let me have the name of the field you want to sum.

     

    Thanks John

    • Central Park Furnishings
    • Michael_McKenna
    • 3 yrs ago
    • Reported - view

    Hi John, l have a table called "Invoices" and a table called "Customer"  with the link from Customer to invoices.
    The field l want to total is called 'Total inc GST' 

    The Customer is called Shop Sale or Customer Id 133

    I have a view called "Reports Table" and have a formula field called Shop Sales. 
    My Sales are made up of clients with large jobs and by Shop Sales and l can easily get the Total sum of all sales in a date range but would like to extract the total of Shop Sales within a certain date range. 
    hope this helps. 
    'kind regards 

    'Michael 

    • John_Halls
    • 3 yrs ago
    • Reported - view

    Hi Michael

     

    You haven't said what your link from Customer to Invoices is called, but say it is called Invoices try this formula

     

    sum( Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd].'Total inc GST' )

     

    In your opening question you used select Invoices which is quizzing the table so you would be seeing all invoices in the date range where here you are quizzing Invoices the relation and so it's already scoped to invoices belonging to the current customer.

     

    Hope that makes sense.

     

    Regards John

Login to reply

Content aside

  • 3 yrs agoLast active
  • 3Replies
  • 413Views