Help

record count with filters - looking to get a number for n'th record in category

Topic Labels: Formulas
168 3
cancel
Showing results for 
Search instead for 
Did you mean: 
l8
4 - Data Explorer
4 - Data Explorer

i have a record table like this (simplified)

idcreated at (datetime)category(text or link to category table)contentcatrgory record count (?)
12025-1-2cat-1 2
22025-1-1cat-2 1
32025-1-1cat-1 1

my idea ist to use a formula like this:
count records, where category = category and {created at} <= {created at}

somehow, all my approaches fail. tried rollup and formula.
if it helps, category fields are linked to another "categories" table. but i could not filter over linked cecords, because that would require linking two times -  to the categories table and then back.

i want to avoid using an automation, because i have a large number of existing records, and an automation seems hacky for me. if there is no other way, i'll go this route, though.


i'd be glad for a hint or a lin to a documentation for a similar case 🙂





3 Replies 3
Alexey_Gusev
13 - Mars
13 - Mars

Hi,
If you want to avoid automation, I suppose you want to do it as one-time action on the current table.
you need some unique id for that, actualy record_id is ok too, but you can use any.
- sort table by datetime acending. 
- create linked field to a new table
- copypaste category field there
- switch to new table (a unique list of categories and many links)
- add unique_id as lookup
- switch back to main table
- add lookup_of_uniqueID as lookup ('Lookup2')
- craft formula to found a place of unique_id in Lookup2 (all ids in that category)
for example, cut left part (get number of chars by find) and count commas in it
like
LEN( LEFT(LOOKUP2, FIND(unique_id,LOOKUP2) ) - LEN (
SUBSTITUTE(  LEFT(LOOKUP2, FIND(unique_id,LOOKUP2) ) ,  ' , '  , ' ' ) )
+1


I just seen you mentioned Categories table. You can use it as well instead of new table. But probably you need to relink (add temporary formula that copying linked field, switch formula to static text type, clean whole linked field, in date sorted table, copypaste static field to link field, delete temporary field) to guarantee that links are in correct order.

thank you for answering! i have 10 to 50 new records coming in every day. manually copying and pasting is a no go since i want all human interaction to be as minimal as possible.
I think i will try to do a script approach, that can calculate either only new records or all records .
all sorting, copy-pasting, linking and new lookup fields just for getting this one value seems alot. i think the base will not look lean and clean.

Of course, method I described only applicable to a static data.
When you have new records everyday, you can use your Categories table. Of course you need to provide that all new records in table will be linked to a categories table.
So, here we are

Alexey_Gusev_0-1738516788182.png

By way, answering to you, I realized that lookup needs additional function to transform into string, like ''&LOOKUP2 instead of just LOOKUP2. But then I decided to play with ARRAYJOIN and found that much easier solution exists The function has second parameter - divider, which is comma by default. So if we set empty string as divider, and apply FIND, result will differ from 'comma-divider' according to a number of values before string we search.
                                    12345                                        1234
In simple words, in  'AAA,BBB,CCC,DDD'   and  'AAABBBCCCDDD" ,  if we find BBB, we get 5 and 4 . 5-4=1
AAA is 1 & 1  ,  DDD is 13 & 10 , 13-10=3. Adding one, we get a position of element in list.

 

 

FIND(unique_id,ARRAYJOIN(LOOKUP2))-
FIND(unique_id,ARRAYJOIN(LOOKUP2,''))+1

 

 


It's even easier considering Lookup fields now has built-in sort - that's really one of the greatest improvements in last months

Alexey_Gusev_1-1738517961773.png

 I used this function for unique_id for this test table but you should craft something more suitable for your needs. The goal is to exclude cases like when you can find 'ID12' in 'ID120'

 

'ID'&RIGHT('000'&Name,3)