count rows with filter condition within unfiltered list

Fabian Koenig shared this question 13 months ago
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

Comments (5)

photo
1

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:

/lUPYAAOApUfZQDmUPAACeEmUP5VD2AADgKVH2UA5lDwAAnlJZ9v8BGCFTC7w4aeAAAAAASUVORK5CYII=

Here is the test data I used:

/t4HrtcQwwQO4Xhs+wHtimOABeB8+wHtimOABeB8+4HkrYpjgAXgfPuB5K9i+2qKtYkQh8D7cKxxvoj18gKWA9wAbAe8BNgLeA2wEvAfYCHgPsBHwHmAj4D3ARsB7gI2A9wAbAe8BNgLeA2wEvAfYCHgPsBHwHmAj4D3ARsB7gI2A9wAbAe8BNgLeA2wEvAfYCHgPsBHwHmAj4D3ARsB7gI2A9wAbAe8BNgLeA2wEvAfYCHgPsBHwHmAj4D3ARsB7gI2A9wAbAe8BNgLeA2wEvAfYCHgPsBHwHmAj4D3ARsB7gI38H1RHrDDp9NXTAAAAAElFTkSuQmCC

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:

/ADAEQO4QcAiBzCDwAQOf8fxYBGfVK1OCcAAAAASUVORK5CYII=

I hope that helps you meet your reporting requirements!

regards,

David

photo
1

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:

4dbae3cbf919a15c6a0930fa8b45b211


and the desired analysis would be:

bc92a8211dbf1a2ddaea63d677ceebb3


So i would like to

  • count the number of invoices for each client,
  • sum the revenue across invoices and credit notes,
  • and ultimately calculate the average per order, which is the sum of invoices and credit notes per client divided by the number of invoices.

Any advice as to how this can be accomplished in YellowFin?


Thanks & Regards

Fabian

photo
1

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:


/AUr9Sf1GkKF+AAAAAElFTkSuQmCC


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):

/BsHFfCPG2iEIAAAAABJRU5ErkJggg==


and then the report looks like this:

/Pb61+YQCCMEVCwAAoBHBAgAAaEOwAAAA2hAsAACANgQLAACgDcECAABoQ7AAAADaECwAAIA2BAsAAKANwQIAAGhDsAAAANoQLAAAgDYECwAAoA3BAgAAaEOwAAAA2hAsAACANgQLAACgDcECAABoQ7AAAADaECwAAIA25woW95shWAAA0FPOFSzMYAgWAAD0CIIFAADQpr1TIbaw3yYAAOgErlgAAABtCBYAAEAbggUAANCGYAEAALQhWAAAAG0IFgAAQBuCBQAA0IZgAQAAtCFYAAAAbQgWAABAG4IFAADQhmABAAC0OU+w+P+N5901ecbTLwAAAABJRU5ErkJggg==


I hope that helps you meet your reporting requirements!

regards,

David

photo
1

Hi Fabian,

just wondering how you got on with this, and whether you liked my suggestion or not?

regards,

David

photo
1

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