Help with last Formula...
let xFrom := From;
let xTo := To;
let Cus := CUSTOMER;
sum(Invoices['Date of Invoice' >= xFrom and 'Date of Invoice' <= xTo].Customer.133)
This does not return the amount shown in the view of Total Shop Sales but does return an amount. I have used this formular in all other fields where I want to exract Totals within Date Range.
The above formula is the only one that does not return value in a dollar amount, where all othres do and the amount is different to the sum view.
Customer.133 is a Customer with multiple Sales againt it.
Not sure what is wrong but would appreciate the help in getting this last piece done.
Can you add an extra onto...... sum(Invoices['Date of Invoice' >= xFrom and 'Date of Invoice' <= xTo].Customer.133). and include the Total inc GST for this Customer.
Formula field name is called 'Shop Sales'
Regards
Michael
13 replies
-
Hi Michael
Didn't I answer this a couple of days ago?
---
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
---
Regards John
-
Hi John, the formula you gave gives me a Total for all invoices, I am looking for a formula to sum all invoices of one customer.
I did try re
sum( Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd].'Customer.133' ) but thsi did not work.
that's why I was asking if you can add two paramaters at the end. like .'Total inc GST' and 'Customer.133')
but this does not work either.
Regards
Michael
-
-
-
You can see from the screen shot that the total in the View for 'Total inc GST' is different to the sum in 'Shop Sales'
The screen Shot above is when I just have the .'Customer.133')
The Formula you gave me is the same as the 'Total' Field which gives me the total Sum of invoices.
Hope these pics clear things up.
Regards
Michael
-
Michael, it is just logic. Try to understand the code below.
This way along with the date filter, we tell Ninox to filter with selected CUstomer i.e. 'Shop Sale'.
let myCust := Customer;
let xBeg := From;
let xEnd := To;sum(Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd and Customer = myCust].'Shop Sales' )
-
Ooops! Sorry...
let myCust := Customer;
let xBeg := From;
let xEnd := To;sum(Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd and Customer = myCust].'Total inc GST' )
or
sum(Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd and Customer = myCust].'HERE GOES THE NAME OF THE FIELD TO SUM' )
-
Yay... thank you for helping with that. I know that it's all logic but I could not make it work. You can spent hours down the rabit hole that is Ninox.
Much appreciated and hope that this might help others.
Regards
Michael
-
Hi Michael
I didn't realise you were using a dashboard.
I can't see why Nick's code is not working. Are you getting an error when you type the code into the formula or is it 'working' but producing incorrect figures?
Thanks John
-
Can you post a copy of the code you used in the view. We can use that as the basis for your code to sum values.
-
Looking again at the code, as you are quizzing the Invoices table (not the relationship) you need to include the word select in your statement, and your screenshot shows the customer relationship as CUSTOMER, so maybe make a couple of changes to
let myCust := CUSTOMER;
let xBeg := From;
let xEnd := To;sum((select Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd and Customer = myCust]).'Total inc GST' )
Regards John
-
Hi John,
this is the formula that was suggested first, which only gave me the total of all invoices. I was really after a formula to sum all Customer.Shop Sales which is what he kindly supplied in hes last post. Which did the job pefectly.
sum( Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd].'Total inc GST' )
let myCust := Customer;
let xBeg := From;
let xEnd := To;sum(Invoices['Date of Invoice'>= xBeg and 'Date of Invoice' <=xEnd and Customer = myCust].'Total inc GST' )
-
Hi John,
This is the formula used in the View Table.
let myCust := CUSTOMER;
let myFrom := From;
let myTo := To;
select Invoices where Customer = myCust and 'Date of Invoice' >= myFrom and 'Date of Invoice' <= myTo
Content aside
- 3 yrs agoLast active
- 13Replies
- 702Views