Aggregate data after query with multiple groupings

Stefan Klein

Hi there,

I'm stuck for quite a while on this problem and hope you can kick me in the right direction. I'm building a billing system for an audiobook distributor. They publish audiobooks for their publishers on two platforms (Bookwire=bw and Audible=ad). They get the sales data every quarter and must calculate the total share per publisher and platform.

I have a table with all the sales data. I also have a table with all information regarding every single book, where we find the agreed share allocation key (e.G. 70% for the author).

In order to calculate the payout for the publisher, I query all unbilled sales data, group them by

• publisher
• platform
• year
• quarter
• book

and aggregate the sum of the royalties per platform:

Then I loop over the output and calculate the payment (aka multiply the royalties with the allocation key of the book).

Now I have the payment for every publisher per quarter, platform, and book. But I need to cumulate it one step further and sum up the royalties so that they're not per book anymore. And here I struggle.

In the end, I need a result like this:

Year___Quarter_Invoice Text__________Amount
2023___Q1______Payout Bookwire_____123.48
2023___Q1______Payout Audible_______123.48

How can I summarize the "bw_total_royalties" and the "ad_total_royalties"?

I tried to find all unique publisher Id's and loop over those to find all connected items in the array from the query and go from there, but it didn't work.

I guess the solution is not so hard to create, but I need a fresh view on the problem :-)

Any help is appreciated.

Thanks in advance,

Stefan

Liz Anaya

Hi @kleinbits !

We are hosting office hours tomorrow. If you have the opportunity to join us, you can register here. It could be a great time to get fresh views of the problem. :)

Pawel Magdanski

Hi,

Can you share thee loop you created? Because from looking at you final json structure it looks like it's absolutely possible to loop by publisher and sum up the royalities using sum filter. What kind of error are you getting? It might be connected to the fact that some records will have null values or missing parameters.

Stefan Klein

Hi @Pawel Magdanski,

I didn't get an error, but I couldn't loop through the publisher and sum up the values grouped by year and quarter. Instead, I tried another approach:

After calculating the royalties payment per book, I loop through the result and use the Find all Elements function to get all elements with the same publisher, year, and quarter.

Then I loop through that result and add the royalties to a variable. Then I remove the last element from this array (to get rid of the other elements I don't need anymore), update the royalties in this object and push it into an array. This works so far, I get the sum of the royalties per quarter, but there's one issue remaining.

When I use the Find all Elements function, I need to remove the found elements from the array to prevent it looping through those elements again. I tried the remove function, and the diff_assoc filter, but that didn't work. No error, it just did not remove the elements from the array.

Any idea how I can do that?

Also, my way surely is not the most elegant way. Any advice on how I can do this better?

Thanks for helping out 🙏🏼