only show 5 highest values?

[dansmith65]

In the (FM12) file attached, can anyone think of a clever way to limit that chart to only the 5 highest results?

chartTest.zip

[dansmith65]

I was hoping to come up with a way to do this with just relationships, calculations, and summary fields. But the Fast Summary method seems to work well for this, and run plenty fast. I attached my modified test file for anyone interested. I used this blog post to help implement Fast Summaries: http://www.filemakerhacks.com/?p=2098

chartTestv2.zip

[mr_vodka]

You could also use something like this and then parse out the X and Y values from it.

LeftValues (



ExecuteSQL ( "

SELECT Count (c_date) as thedate, Region

FROM eqs7day_M1

WHERE c_date >= ? AND c_date <= ?

GROUP BY Region

ORDER BY thedate DESC

"

; "" ; ""; global::dateStart; global::dateEnd )



; 5

)

or

LeftValues (



ExecuteSQL ( "

SELECT Count (c_date) as thedate, Region

FROM eqs7day_M1

WHERE c_date BETWEEN ? AND ?

GROUP BY Region

ORDER BY thedate DESC

"

; "" ; ""; global::dateStart; global::dateEnd )



; 5

)

[dansmith65]

Thanks, that's a good idea. I wonder if Fast Summaries or SQL is faster? If I am feeling ambitious some day, I might test it.

[dansmith65]

Thanks, that's a good idea. I wonder if Fast Summaries or SQL is faster? If I am feeling ambitious some day, I might test it.

I just did that speed test - and the results were shocking...

With the database hosted on my local computer:

Fast Summaries: 0.05 sec

SQL: 0.33 sec

On my client's database hosted on the web, accessed remotely: (different database than chartTest, attached above)

Fast Summaries: 1.40 sec for first run, 0.14 all additional runs

SQL: ~200 sec for first run, 0.22 all additional runs

So, it seems like using SQL is causing all records to be downloaded, whereas Fast Summaries does not. Once the data is downloaded, running the test additional times is quick using either method - all it takes to make it slow again is to close the database, then log into it again.

[mr_vodka]

Interesting. I really wish FMI would have some kind of whitepaper on how much is actually processed on the server vs client using ExecuteSQL.

Curious though... do you have a SQL plugin? Also what if using ODBC? These would be good to benchmark against as well, would you be able to test those?

I do know that the aggregation happens on the client side which at DevCon I was persistent that FMI try and look to improve / change this model in the future as more and more people start using WAN especially GO solutions. Using ODBC though it should happen on the server side.

[dansmith65]

I tested with BaseElements plug-in when I did the tests last night - it was so close in processing time it wasn't worth mentioning it separately.

No, I won't be able to test with ODBC on the same database that I ran the previous WAN tests on.

[org]

I was hoping to come up with a way to do this with just relationships, calculations, and summary fields. But the Fast Summary method seems to work well for this, and run plenty fast. I attached my modified test file for anyone interested. I used this blog post to help implement Fast Summaries: http://www.filemakerhacks.com/?p=2098

Thanks Dan thats a neat technique, esp speed wise.

I have a need along similar lines, I was going down the path of convoluted, complex looping scripts building up vars but this should help speed up the process (and development).