Forum

This content is now out of date.

Visit Our Community

Filter on Percentage

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.


Forum image


and here are the results without filtering:


Forum image


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


Forum image




Forum image


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:


Forum image



Forum image


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 SUMS

1
Numerator
numeric


2
Denominator
numeric



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


MySQL
SQL Server
PostgreSQL
Oracle
numeric
[/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

[code][/code]

and restart Yellowfin.

No guarantees with this but I guess it's worth a try.

Please let us know how it goes.

regards,
Dave