Filtering "something" based on rating values to create some type of sorting?

1

Hi there, Dear Members of the Community :grin: !

Sorry for this very unclear title but I have no idea anymore how to describe, shortly, what I’m trying to do :sweat_smile: .

So, in the sample doc below, I’ve got one table Tests linked to the second table Ratings by a LookUp Field.

Now, what I would like to do would be to create a field like in the Expected Results which would be a Cumulative Count() (Sum() seems to work too) of the filtered ratings where Ratings=5 and Ratings=4 (only those two) in the corresponding rows.

I’ve done that in R=5 and R=4 separately.

Now, what I would like to have is put those field together in a specific order :

  • First all the results for Ratings=5 (1,2,3)
  • Second all the results for Ratings=4 (1,2)

Which should look like : 1,2,3,1,2 and from there having a real consecutive list of numbers : 1,2,3,4,5 with 1,2,3 corresponding to Ratings=5 and 4,5 for Ratings=4 still in their corresponding rows.

This field would be to create a field I could use to sort the table Tests :blush: .

I tried to search the Community before asking but as I spent all day on this, I got completely lost and don’t even know what I’m suppose to search or find :sweat: .

Is this doable :blush: ? I don’t see any reasons why not but I could be deeply wrong :innocent:.

If anyone does have an idea :bulb: or a solution, like always, it will be profoundly appreciated :grin: !

2

Hey I’m not sure exactly how you want the output, but if you want to get the list 1,2,3,1,2 you can use the formula:

listcombine( Tests.[R=5].Filter(CurrentValue.IsNotBlank()), Tests.[R=4].Filter(currentvalue.IsNotBlank()) )

If you want to get the ‘row’ outputs:

ListCombine( Tests.Filter([R=5].IsNotBlank()), Tests.Filter([R=4].IsNotBlank()) )

Good luck!

3

Hi @Johg_Ananda :grin: !

Well, this wasn’t what I was trying to do but it definitively helped me to get back on my feet and find the solution :tada: !

I lost, once again, track of the simple things :innocent: and your reply helped me to find the right track to follow :grin: !

Thank you very much for this :grin: !!!

Just in case someone else would be stuck on this too :yum: , I’ll leave my solution here :blush:

SwitchIf(thisRow.Ratings=[5],[Tests 2].Filter(Ratings=[5] AND Sort<=thisRow.Sort).Count(),thisRow.Ratings=[4],Sum([Tests 2].Filter(Ratings=[5]).Count().Max(),[Tests 2].Filter(Ratings=[4] AND Sort<=thisRow.Sort).Count()))