SQL query to add values for last 4 quarters

[sam81]

I cannot figure out how to sum values for last 4 quarters. FMP has function to SUM all values but not selected values..How do i sum the last 4 quarters for the given product...

 

unique id      product id     quarter id         sales

1                         1             2013-q1            10

2                        1             2013-q2             20

3                         1             2013-q3            30

4                        1             2013-q4            40

5                        1             2014-q1            50

6                        1             2014-q2             60

 

7                         2             2013-q1             70

8                        2            2013-q2             80

9                         2             2013-q3             90

10                       2             2013-q4            100

11                      2            2014-q1             110

12                       2             2014-q2             120

 

 

i created a ExecuteSQL query 

 

ExecuteSQL ( 

"SELECT SUM(TOP 4)

          IMS data::Quarter ID, 

          IMS data::Units sold USA

 ORDER BY

          IMS data::Quarter ID DESC";

      "¶" ;"" )

[comment]

I cannot figure out how to sum values for last 4 quarters.

 

IMHO, the best way would be to find the sales in the last 4 quarters, then summarize the found set by product. Alternatively, you could use a filtered relationship/portal to display only sales from the last 4 quarters in a layout of Products. Filemaker is not a SQL database and SQL should be your last choice, not first.

[Wim Decorte]

I don't agree that SQL should be your last choice.  It IS a native function and allows for a much more light-weight schema.  And they are blazingly fast when used under the right circumstances.

 

A working SQL query would be:

 

SELECT quarterID,SUM( sales )

FROM "IMS Data"

WHERE ( quarterID = '2014-q2' OR quarterID = '2014-q1' OR quarterID = '2013-q4' OR quarterID = '2013-q3' )

GROUP BY quarterID

ORDER BY quarterID

 

Which will give you, based on your data:

2013-q3,120

2013-q4,140

2014-q1,160

2014-q2,180

 

You can avoid the multiple ORs with an IN clause if you'd like

[sam81]

Hello 'Comment',

 

i used  filtered relationship/portal to display only sales from the last 4 quarters in a layout of Products....then i duplicated the portal and used "summary field" to find sum of last 4 quarters for the filetered portal [i did not knew FMP had this functionality...thanks a lot !]

 

but problem lies that i cannot use this 'summary field' for further calculation....when i use this calculated field somewhere else it gives total different value ..how do i resolve this..or do i need scripting for this ?

[sam81]

hello Will,

 

for Sql query i want the query to reply the sum of last 4 quarters for each product..so result table should be 

 

product       Sales number 

1                          180 (30+40+50+60)

2                          420 (90+100+110+120)

[comment]

but problem lies that i cannot use this 'summary field' for further calculation....

 

In order to use the sum in another calculation, you would need to add a filtered relationship (not a filtered portal), using another TO of the Sales table, and sum the related records using the Sum() function, not a summary field.

 

Alternatively, you could add add an (unstored) calculation field in the Sales table that returns the sales amount if the quarter is among the last 4 quarters - then sum this field in the Products table, using the existing relationship.

 

 

Both of these will require more schema resources than ExecuteSQL(), so this may well be a case to consider SQL after all.

[Wim Decorte]

hello Will,

 

for Sql query i want the query to reply the sum of last 4 quarters for each product..so result table should be 

 

product       Sales number 

1                          180 (30+40+50+60)

2                          420 (90+100+110+120)

 

SELECT productID,SUM( sales )

FROM testsalesdata

WHERE ( quarterID = '2014-q2' OR quarterID = '2014-q1' OR quarterID = '2013-q4' OR quarterID = '2013-q3' )

GROUP BY productID

 

returns

1,180

2,420

[sam81]

Hello Will,

 

When i use your code in sql compiler it gives me correct results...but when i used in FMP it gives me error...not sure what the issue is 

 

ExecuteSQL(

 

"SELECT SUM( sales )

FROM testsalesdata

WHERE ( quarterID = '2014-q2' OR quarterID = '2014-q1' OR quarterID = '2013-q4' OR quarterID = '2013-q3' )";

"";"")

 

Also, wanted to clarify that I am using it for "Specify calculation".......not sure what the error is for 

 

thank you 

Also, what to specify that I am using it in the 'Specify calc

[Wim Decorte]

Note that the field names and table name are from my test file, you have to use your table and field names.  And if you use spaces or leading underscores in your names, the field names and table name need to be in quotes