Forum

Filter on Percentage

7 November, 2014

Hi

I would like to know how to filter on percentages when they are summed, for example you have a sales order and you sum the lines to get the total sales and total cost amounts, then do a calculation to get the GP%. I would then like to show only the orders which have a GP% less then 5% for example.

Problem is you cannot add an aggregate to a where statement.
Is there a way around this?

Hi Jonathon,

here is an example that sums the Cancellation Fee, and also the Invoiced Amount, then there is a Calculated Field that calculates SUM(Cancellation Fee)/SUM(Invoiced Amount)*100.

and here are the results without filtering:

And then I can use that calculated field to filter the results to show only Athlete Regions whose percentage is greater than 1 percent:

I hope that helps your report, if there are any further questions then please let us know.

regards,
Dave

Hi Dave

Thanks for that, I have tried your suggestion and the report seems to work and filter by the percentage, but the other filters I have disappear and I get the following error in the yellowfin.log

"YF:2014-11-14 10:35:38:ERROR (DBAction:doSelect) - Error occured selecting data: com.ingres.gcf.util.SqlEx: line 1. Illegal use of aggregate function in a WHERE clause.
com.ingres.gcf.util.SqlEx: line 1. Illegal use of aggregate function in a WHERE clause."

If you add another filter in your example for say athlete region does it still work?

Regards

Jonathon

Hi Jonathon,

Well I added a 2nd filter and got very close to the error you were getting:

I wonder if the difference in the error message is due to the different databases we are using perhaps?

Anyway, I've raised a product defect for this issue (Support Task 178748) and added it to the agenda for next week's dev meeting.

I know it's not exactly the error you were getting but I feel that they are closely related and fixing this one should fix yours, what do you think?

regards,
Dave

Hi Dave

Thanks for that, once the fix is put through I will test it and let you know if it solves my issue as well.
Hopefully they are the same.

Thanks for the quick response.

Regards

Jonathon

Hi Dave

Do you know the status of this, I see it wasn't put through in the November release.
Is there any other way around this? I really need to be able to complete this report.

Regards

Jonathon

Hi Jonathon,

I've had a look at the status of the task and unfortunately it looks like the developers haven't been able to get around to it yet. So I've raised its priority to HIGH - hopefully that will get it done more quickly.

In meanwhile you could try creating a custom function which would then be available as a Pre-Defined function in a report-level calculated field or view-level calculated field Formula Template, either of which are able to be used as a filter.

To get you on your way with this I've created something similar to what you might be trying to achieve, maybe you could modify it and then use it:

[code]

% of 2 SUMS1Numeratornumeric

2Denominatornumeric

SUM($1) / SUM($2) * 100

MySQLSQL ServerPostgreSQLOraclenumeric[/code]

just paste the above code at the end of your /appserver/webapps/ROOT/WEB-INF/custom-functions.xml file just before the final line