count rows with filter condition within unfiltered list
Answered
Hi there!
How can I count rows in a table based on a filter condition, while basing the query itself on an unfiltered query?
Use case:
- I have a list of invoices and credit notes.
- I am running an aggregation sum([net_revenue]) group by [client_id]
- invoice amounts are positive and credit note amounts are negative
- => so I end up with a list of [client_id]'s next to their total revenue (= invoices - credit notes)
- Now I would like to add a column displaying the count of INVOICES only (column [type] = "invoice").
In SQL I'd simply add an inline-select and count filtering the inline-select by column [type].
I can't however find a way to accomplish this in YellowFin.
Thanks & Regards
Fabian
The same question
Hi Fabian,
I was able to do this by just using a calculated field of format type SIMPLE and used a COUNT in the formula, and then applying a Sum as the Total Aggregation to the column:
Here is the test data I used:
as you can see, client 1 should have a total revenue of 270 and client 2 should have 630. And if I add a column displaying the count of INVOICES only then it should be 4.
And that's what the Yellowfin report is showing:
I hope that helps you meet your reporting requirements!
regards,
David
Hi Fabian,
I was able to do this by just using a calculated field of format type SIMPLE and used a COUNT in the formula, and then applying a Sum as the Total Aggregation to the column:
Here is the test data I used:
as you can see, client 1 should have a total revenue of 270 and client 2 should have 630. And if I add a column displaying the count of INVOICES only then it should be 4.
And that's what the Yellowfin report is showing:
I hope that helps you meet your reporting requirements!
regards,
David
Hi David,
thanks for the prompt reply. However, the data source isn't quite what real-life gives me.
For example, you anticipated that the number of invoices and credit notes is always equal (you're actually combining invoices and credit notes into one row).
The source data would much rather look like this:
and the desired analysis would be:
So i would like to
Any advice as to how this can be accomplished in YellowFin?
Thanks & Regards
Fabian
Hi David,
thanks for the prompt reply. However, the data source isn't quite what real-life gives me.
For example, you anticipated that the number of invoices and credit notes is always equal (you're actually combining invoices and credit notes into one row).
The source data would much rather look like this:
and the desired analysis would be:
So i would like to
Any advice as to how this can be accomplished in YellowFin?
Thanks & Regards
Fabian
Hi Fabian,
thank you for giving me some sample data.
The way I would approach this report is to add a virtual table to the view which counts the invoice records and does an inner join to the bookkeeping table on client_id:
and then create the calculated field for the "average revenue per order" as follows (I know you know how to do this, but I am including it just to show you that if you ever have to divide an integer by an integer in a calculated field and want the answer to be a decimal then you must multiply the numerator by the float 1.0 which causes an implicit cast to occur):
and then the report looks like this:
I hope that helps you meet your reporting requirements!
regards,
David
Hi Fabian,
thank you for giving me some sample data.
The way I would approach this report is to add a virtual table to the view which counts the invoice records and does an inner join to the bookkeeping table on client_id:
and then create the calculated field for the "average revenue per order" as follows (I know you know how to do this, but I am including it just to show you that if you ever have to divide an integer by an integer in a calculated field and want the answer to be a decimal then you must multiply the numerator by the float 1.0 which causes an implicit cast to occur):
and then the report looks like this:
I hope that helps you meet your reporting requirements!
regards,
David
Hi Fabian,
just wondering how you got on with this, and whether you liked my suggestion or not?
regards,
David
Hi Fabian,
just wondering how you got on with this, and whether you liked my suggestion or not?
regards,
David
Hi Fabian,
there has been no response for over a month now so the ticket will be closed, however, if you'd like to reopen it then all you have to do is to simply add a new post to it and its status will automatically change from "Answered" to "In Progress" and will appear in my work list.
regards,
David
Hi Fabian,
there has been no response for over a month now so the ticket will be closed, however, if you'd like to reopen it then all you have to do is to simply add a new post to it and its status will automatically change from "Answered" to "In Progress" and will appear in my work list.
regards,
David
Replies have been locked on this page!