Filter Data and Count

[atiwat_tangchaiwatta]

Hello , Community , Now I am working on inventory and Sale tracking features
I wonder if

1. Can we filter Date in glide ( Only this week) , I want to filter only activities that happen in this week
2. Is there any ways to set count condition , I am working on Sale tracking project ( Every time sale visit customer , they need to check in by submitting form in our apps with fundamental information) , And I want to count how many total customers they visit weekly ( In Excel I use count if with date condition but I wonder is there more simple way to do in Glide ?)

[Uzo]

Yes… click my avatar and link to my website (it is a Glide Page)… then you click again… you will see that it will track you

[ThinhDinh]

@atiwat_tangchaiwatta

Can we filter Date in glide ( Only this week) , I want to filter only activities that happen in this week

If you mean the native filtering, then you can use 2 math columns to help with that.

In the table where you record activities, I assume you have recorded the time that the activitiy happened, then the formula for the math column would be:

YEAR(T)*100 + WEEKNUM(T) - YEAR(N)*100 - WEEKNUM(N)

With T being the timestamp of the activity, and N being the “now” value.

If it’s the current week, you would get 0 as the result.

[ThinhDinh]

@atiwat_tangchaiwatta

Is there any ways to set count condition , I am working on Sale tracking project ( Every time sale visit customer , they need to check in by submitting form in our apps with fundamental information) , And I want to count how many total customers they visit weekly ( In Excel I use count if with date condition but I wonder is there more simple way to do in Glide ?)

So how do you want to present that info in the front end? Do you present it as a list of weeks and their corresponding count?

[atiwat_tangchaiwatta]

This is EPIC , really thanks for your answer , I got noob questions

1. How to create Now Date in Glide , I create Date column there is no Now option
2. On Google Sheet , I apply =NOW() which it working but when I apply ArrayFormular =ArrayFormula(NOW()) It doesn’t auto fill those below and new added row

[atiwat_tangchaiwatta]

1. I want to count total of rows that happen both weekly and monthly Ex A sale has submit 10 in this week , 45 / month like that
2. It would be nice if there’s Number + Customer
   Ex : 1. Customer A
   2. Customer Jack
   3. Customer John

[Darren_Murphy]

@atiwat_tangchaiwatta

How to create Now Date in Glide , I create Date column there is no Now option

Use a Math column, with Now as a replacement.

[ThinhDinh]

@atiwat_tangchaiwatta

I want to count total of rows that happen both weekly and monthly Ex A sale has submit 10 in this week , 45 / month like that

So you only need the numbers to show stats of “this week”, “this month”?

[atiwat_tangchaiwatta]

Exactly

[ThinhDinh]

I suppose this is what you can do:

• In your users table, create a math column to calculate the current week’s numerical value:

YEAR(N)*100 + WEEK(N)

With N being the “Now” value.

• In your users table, create a math column to calculate the current month’s numerical value:

YEAR(N)*100 + MONTH(N)

With N being the “Now” value.

• In your users table, create a template column joining the user’s rowID/email with the current week’s value above.

• In your users table, create a template column joining the user’s rowID/email with the current month’s value above.

• In the sales record table, do the same process to create 4 columns, but instead of using “Now” in the math columns, use the timestamp the record was added.

• Create 2 relations from the 2 templates in the Users table to its corresponding templates in the Sales record table, make them match multiple rows.

• Create 2 rollup columns on top of 2 relations to count the number of matching rows.