I have a column " date subscription all customers",
I want to find the customers subscribed during a certain period (in glide table)
for example:
I need to create a column
( If “Subscription date” is on or before “12/31/2021” = Q1 )
How can I do that?
Any ideas?
That’s probably not the best approach, because if the Subscription date is (for example) 15th November 2021, then it would also get counted as Q1. And I assume that’s not what you want.
What I would do is just use the month of the subscription date to determine the Quarter number. So, something like this:
Month(Date). This will return a number between 1 and 12.This is not exactly what I wanted.
I need to find all subscribed customers until 31/12/2021
There are customers whose subscription date is in 2021, 2020 and 2019
ــــــــ
(Sorry, Q1 was to express the result only )
Right, okay.
So just create a math column that extracts the year from the subscription date: Year(Date).
Then use that in the if-then-else column:
okay, can I find all the customers who subscribed before March 31, 2022.
Do you have an idea about that?
Do you have a column anywhere that stores that Dec 31st or March 31st date? I would think you could just point to the column that contains that value. It could be as simple as a date that stored in a single row table somewhere, and then brought into your current table using a single value column so it’s populated on all rows, and then your IF column can compare the two dates.
Hola!
Be careful with your date format and comparisons sorting (order). I see you are using dd/mm/yyyy format and its fine but Glide internally uses mm/dd/yyyy instead and of course, 31/12/2021 is quite different than 12/31/2021.
You will get wrong results if your users use a different date format than the one configured in your APP. In other words, the date comparisons will work fine if user device are working with US date format only.
In these cases, the best way is convert dates to numeric values with yyyy/mm/dd format:
This only applies so far working with current plugins.
Saludos.
Your math to parse apart a date works because glide already knows what parts of that date are the year, month, day, etc. If glide already knows that it’s a date and how to parse it apart, then it seems like extra work to convert dates to a numbers, just to compare them as numbers when they could have been compared as dates.
Date comparisons should work fine in glide, regardless of format and region, as long as you stick to the standard computed columns, including the IF column. Date columns have a datatype of date, so with an IF column, glide will treat dates as dates regardless of the user’s local format. Even a single value column will follow the datatype of it’s source column. I don’t see any reason why an IF column wouldn’t work, as long as both dates being worked with, come from date columns.
It’s the experimental plugins that you have to be careful with. Especially the plugins that deal with dates. The reason for that is because the plugins are passing date dataypes into the code as strings. Once a date datatype is converted to a string datatype, then it loses it’s true date value and relies on the code to know beforehand, what the format of the date was. If the date (as a string) is ambiguous and can be interpreted multiple ways, then it becomes impossible to determine the date.
As long as any date is contained within a date column type, and if any of the standard computed columns are used instead of the more experimental plugins, then a user’s regional date format shouldn’t have any bearing on if a date comparison works or not. The underlying date is still a date data type. It’s the forward facing formatting that will adapt based on the end users region/local.
Hola Jeff!
I think I confused a data Comparison with a data Sorting … wtf! 
You are right, any Math or IF-Then column will work correctly with dates no matter its format. The plugins are whom cause the headaches.
Let me modify part of my last writing above to avoid misunderstandings.
Feliz día.
