AnsweredAssumed Answered

Upper quartile calc on a subset of data

Question asked by Stu412 on Jun 16, 2016
Latest reply on Jun 17, 2016 by user19752

Hi all,

 

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:

 

North

     Beans

     Potatoes

     Oranges

 

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:

 

FileMaker Custom Function:ThirdQuartile ( valueList )

 

I'm struggling to make this work as, to be honest, I don't know what it expects to go where!

 

Any help appreciated.

 

Thanks

 

 

          

Outcomes