Can I Calc. It? Trying to Compute %on Time

[jhomer]

Having trouble doing what I would assume is an easy task

I have a table of invoice records with these Fields of interest ( invoicedate - invoicequantity - quantityontime)

 

I would like to graph our on Time Delivery % by day but use a execute SQL statement to gather my data.

 

The below query returns a nice list of what I would like to use for dividends and divisors as expected, However when I try to calculate the two summaries my statement returns the ?

ExecuteSQL("Select

sum(quantityontime),

sum(invoicequantity)

 

From ontimedeliverydetail

where invoicedate between '6/3/2013' and '6/30/2013'

group by invoicedate

";"";"")

 

What changes could be made to return a % per day?

 

 

 

 

 

[Darren Burgess]

Are you saying that the exact query you included in your post results in '?'

 

If that is the case then you have syntax error.  Or an error in your fieldnames or table names.

 

I would think you have to resolve that before you attempt the rest of the calculation.

 

Also, I would suggest you use Let to declare the various bits that will be used in your % calc.

 

Darren Burgess

http://www.mightydata.com

[jhomer]

Darren, thanks for the reply.

 

The querry in the OP executes successfully.  It returns a list of the two SUMmed values per day.

 

What I am trying to accomplish is to generate a returned list where each row equates to the %on Time for that day.

6-3-2013, .96

6-4-2013, .95

etc.

 

However:

ExecuteSQL("Select

 invoicedate, (sum(quantityontime) / sum(invoicequantity))

 

From ontimedeliverydetail

where invoicedate between '6/3/2013' and '6/30/2013'

group by invoicedate

";"";"")

yields a ?

 

 

Also as an FYI im editorilizing my query a bit for the sake of this discussion...I have a few custom function built to help me get out of a total literal string.

[Lee Smith]

Have you reviewed the available how-to videos available on YouTube?

 

Also, checkout SeedCode's new upgrade to there SQL Explore. Here a link to is their announcement made yesterday, which basically will help you write the code.

 

Lee

[LaRetta]

Hi JHomer,

 

I am no SQL expert but using ExecuteSQL() to derive your data from an Invoices table which usually means large data-set, will probably not be your best choice.  Why are you not using native FM relationship to handle it?  This is, after all, one of your primary tables in your solution, right?  This functionality seems pretty important, will be frequently accessed, and is a core need.  And ExecuteSQL() is NOT FAST when complex.

 

Also note that if the above is NOT your true calc ... if you also include custom functions ... then we cannot even come close to answering your post. But this may help you understand how your custom functions might need to interact:

 

http://fmforums.com/forum/topic/88982-cast-as-money/

 

I hope you can work through your issue and figure it out.  

[Wim Decorte]

 

where invoicedate between '6/3/2013' and '6/30/2013'

 

 

If your date fields are real dates then using the single quote around them will not work.  SQL interprets that as text strings.

Use ? in your SQL query for both dates and feed the dates as parameters to your query.  That way FM will properly encapsulate the data.

[jhomer]

Lee, thanks for the note on the seedcode update, I'll take a look at that.  LaRetta I have a couple reports that are using flat ol FileMaker techniques to get users the data they whant; I'm just trying to strech the membrane a bit. 

Again I'm editorializing the calc I posted a bit, I was hoping to focus on developing the correct SQL select syntax.