Countif in Glide Table

[ToOFa_Apps]

How to do a countif in a glide table, but with the first data row fixed and the rest of the rows keep being counted as the formula grows. I need a solution for Glide Tables only (not google sheets) Thanks in Advance.

See Example Image

[Darren_Murphy]

mmm, this is a tricky one. I think the biggest challenge with this is that the usual approach of relation plus rollup won’t work. That’s because for each row, you only want to count the occurrences in the current row and all previous rows, right?

Anyway, I have a solution for you. But be warned, it isn’t simple. It involves just about every column type you can think of, and a bit of Javascript thrown in for good measure

I don’t think I could explain it in words, so here is a video:

Here is the Javascript that does the counts from the joined list:

var arr = p1.split(',').map(Number);
const count = (arr, val) => arr.reduce((a, v) => (v === val ? a + 1 : a), 0);
return count(arr, p2) || undefined;

Hopefully somebody else will come up with a simpler solution.

[ToOFa_Apps]

Wow, thanks so much, i’ve been wanting this for years, bummed i didn’t ask before. Okay i’ll try this out. I will likely have to ask questions when it gets to the javascript. Thx

[Darren_Murphy]

Welcome.

To be honest, it’s an ugly solution and I’m a bit annoyed that I couldn’t come up with something simpler.
Just thinking about it, if we’re going to resort to Javascript then we may as well do most of the work with JS and get rid of all those column gymnastics in the middle.

Give me a little bit to figure that out, and I’ll post an update in a while.

@ToOFa_Apps here, try this instead. Just 5 columns needed…

• Use the first 3 columns to create the incrementing row index as I described in the video
• Then a Joined List of all the values
• And then the Javascript column, which takes 3 parameters:
  – p1: the joined list column
  – p2: the Value column
  – p3: the Row Index column

The Javascript you’ll need:

  var arr = p1.split(',').map(Number).slice(0, p3+1);
  if (p2 === 0) { return arr[0] }
  const count = (arr, val) => arr.reduce((a, v) => (v === val ? a + 1 : a), 0);
  return count(arr, p2) || undefined;

This is much cleaner

[ToOFa_Apps]

Yeah definitely less columns and cleaner. I suppose if we’re using JS, might as well use it in the middle columns too. This is great! I’ll definitely have to chat more when it comes to the Javascript. Thanks a million👍

[Robert_Petitto]

This was the issue I was having when trying to figure out streaks. Ended up figuring out a way to do this nocode, but it required quite a few columns to make it happen.

[Robert_Petitto]

Actually with this, you can relate the value column back to itself (multiple). Then do a lookup of the of relation to pull in the Row IDs of the relation and finally a find element index of the lookup columns to find the RowID.

[Darren_Murphy]

@Robert_Petitto

This was the issue I was having when trying to figure out streaks.

Believe it or not, I watched that all the way through
I’m still trying to figure out in my head why you needed all those extra columns to get the current streak. At 32:30 in the video, the first thing that occurred to me was “just use a single value through the relation from the user profile and take the last value”. I’m sure there’s a good reason why that doesn’t work…

oh, and you win the prize for the longest ever column names

[Robert_Petitto]

@Darren_Murphy

“just use a single value through the relation from the user profile and take the last value”

Because the last value/record added might not be the last chronologically

[Robert_Petitto]

Ya…it was a long video—but it took me 2 days of literal staring at the data editor to figure it out.

[Darren_Murphy]

yeah, some crazy logic going on there. I have to admit that I got lost more than once trying to follow along…

[Robert_Petitto]

@Darren_Murphy

I got lost more than once

[chee]

Building on the fundamental building blocks laid out by @Darren_Murphy, I think I come out with an alternative. I wanted to attempt this earlier but the plugin columns were mysteriously missing from my data editor for days. Just got them back hours ago thanks to Glide support team.

[Darren_Murphy]

Nice

[elmerdubon]

Thanks

[ToOFa_Apps]

Wow @chee. It took longer to figure yours out, there is some crazy stuff going on in there, but i love that its a no code solution. Well done and thanks a million. Will test that all out later today.
I’ve been using @Darren_Murphy solution till now and it also works amazingly well. I tried so many solutions in my early days of glide (March 2020) and kind of gave up. But this simple ‘Drag Count’ method (as i’ve been calling it) is really crucial in an app i haven’t been able to create till now (without using a google sheet). It’s so much quicker on a table compared to sheets.
Really good to know we have the tools and the experts available to help with these complex calculations. Thanks guys!!!