Forum Academy Marketplace Showcase Pricing Features

How to group a repeating group by 5 records

Let say I have a list of records - 100 records and sorted by PlayDate DESC - meaning the new record will be on the top while the old is at the bottom. In this table, I have VALUE1 and VALUE2 and I want to group this by 5 so group by 1-5, 6-10, 11-15 and so on. So if it 100 records it will grouped 20 entries. The VALUE1 and VALUE2 will AVERAGE of those.

How do I do this?

I’m appreciated your inputs.

Thanks

Thanks for your post! You should be able to use the “grouped by” feature for this to create groupings within your repeating group. From there, you’ll want to make sure that you appropriately set up your groupings.

I will try this approach and will let you know. So field for Group By then will be?

Hi @eve

In my repeating group, the data source sets to Search for RoundHoles - Owner = Current User; Sort by PlayDate ASC.

The issue that there is nothing to grouped by for as integer sequence number. How do I create a sequence number for this query then first on the fly? Or am I missing something here.

I’m appreciated your feedback.

Thanks for checking in: interesting challenge! I don’t suppose you could keep track of these groupings by assigning each item a “grouping number”? I think that might be easier than trying to group on the fly, but let me know if that wouldn’t work!

You will not be abble to perform this action like this…

But it is doable thru the LIST NUMBERS element (You will find it in the Toolbox plugin).

With this element you will be able to create a second RG based in a Math operation . So, if you want a second RG based in your first RG grouped by 5, you can create a List of Numbers with 20 itens with something like:

First RG'List:count / 5

This will generate a second list with 20 itens. Indexes from #1 to #20. Like you wanted.

After this, you will have to play with the index number of each item in you second RG to be able to do the Math (always looking at the values in your first RG).

Here is your formula base formula:

(((Cell’s index - 1) * 5) + 1) to (Cell’s index * 5)

Index 1:
(((Cell’s index - 1) * 5) + 1) to (Cell’s index * 5)
(((1 - 1) * 5) + 1) to (1 * 5)
1 to 5

Index 2:
(((Cell’s index - 1) * 5) + 1) to (Cell’s index * 5)
(((2 - 1) * 5) + 1) to (2 * 5)
6 to 10

Index 3:
(((Cell’s index - 1) * 5) + 1) to (Cell’s index * 5)
(((3 - 1) * 5) + 1) to (3 * 5)
11 to 15

… and so on…

Use the Search For to find the data in you fist RG.
Use the command :itens from # and :itens untill #to GET exatly the rows you need in your first RG.

And in the end, use the :Group by and the aggregation “Average” to perform the Math!

1 Like

Hi @eve … sorry I don’t quite understand. Can you please elaborate more on this, please?

Hi @rpetribu … thanks for the input. I was thinking something like this previously. But I was attempting to use “group by”, however, no luck as no sequence number that I can group by.

Yeah, you will need to “fake group it” first, doing as I said
It will work. I tested here :+1:

Hi @rpetribu … The downside with this approach is that if you have 100 records and you have a breakdown by 5 records meaning that you will have 20 queries to the database. Is that right?

No… :ok_hand:

Because all the data will already be downloaded into your first RG. The consult is going to be done in this particular object. It is almost instantaneous…

The second RG is just to organize and calculate things stored in RG 1.

Hi @rpetribu … no worries. Will give it a go. Thanks

Hi @rpetribu … I installed the plugin - Toolbox. Use the List of Number element BUT then what do I do next (start number, length of list, and increment)? I don’t quite understand how this is related to Repeating Group!!!