I am putting together a report based on customer sales data which will allow us to compare a customer's own data vs national averages. All customer's data is held on the same data with CustomerID against each record. The report itself runs on a found set of just that customer's data to keep things quick (there are around 8000 records at this stage and still growing). Per customer, I only need to work on a found set of 20 or so records.
The data is shown in groups first, and then descriptions of sales, for example:
In order to show the national averages for bean sales, I've used ExecuteSQL to query the whole of the table with just the relevant Category and Description fields specified.
What I need to show next, for the national level, is the upper quartile price of beans, potatoes and oranges. To do this, and as I understand it, I need to query the entire data set again, sort it low to high, find the medians and go from there. Because I'm working on the entire data set again, I'm thinking an ExecuteSQL would work, but I don't know how to set this up to pull the upper quartile.
I have looked also at the following on BrianDunning.com:
I'm struggling to make this work as, to be honest, I don't know what it expects to go where!
Any help appreciated.
Get all value from table as list, then put it to the cf.
ExecuteSQL ( "
SELECT sales FROM table WHERE Description = ?
" ; "" ; "" ; "Beans" )