Need help counting based on date

Hi, I have table of post names. For each post, there’s a column called “published” that records the date it was published.

For each published date, I want to count the number of posts already published, i.e. any records with published date before this record’s published date.

Any idea how I can do that?

Hi there!

I created an example for you with two options that you can view at the link below:

https://public.getgrist.com/jkm53AcERYo1/Community-947

If you want a running record of all published posts, you can create a summary table. This will count the records where Published is checked or marked True.

image

To add a summary table, click the green ‘Add New’ button > Add Widget to Page > Select Widget = Table > Select Data = table you want to summarize > Click the green ‘Summation’ icon > Group by = Published. This will create a summary table that shows you how many published and unpublished posts you have.

If you want a total of posts published at time of publishing, you can do this with a formula column. Check out the Total Published column in Table1. Here, the formula is:

if $Date_Published == None:
  return ""
all_published_dates = table.lookupRecords(Published = "True").Date_Published
published_date = $Date_Published
result = []
[result.append(date) for date in all_published_dates if date <= published_date]
return len(result)

The first two lines clear errors when there is no date in the Date Published column.

Next, we find all records where Published is True (has a checkmark) then pull the value in the Date Published column for each of these records. We assign this list of dates to the variable all_published_dates.

We assign the value in the Date Published column of this row to the variable published_date. This will allow us to compare date values.

We create an empty list, result.

We iterate through the list of dates in all_published_dates. For any date that is the same or before the date found in this row’s Date Published column, we add that date to our list result.

len() counts the number of records in our list. Here, it counts the records where the Date Published is less than or equal to the Date Published in this row.

Let me know if you have any questions!