I have an application designed for the quota management of a gym. I make a filter [End DateTime]>=TODAY() to know which quotas are active. But I would like to have a view that shows the quotas that are expired and do not have an active quota. I have thought about getting the last cell created in "End DateTime" but I can't get it to work.
How can I do it? I have tried to do a MAXROW but it shows the last date of "End DateTime" of all the users not only of the one in the tab.
In case I have not explained myself I will try to summarize it. I want to have a column that stores the date of the last bookings quota of each client in the Spaces tab (which is the tab table). I could also use a filter. Thanks in advance
I have two tables with the following columns:
Table: Spaces
Columns:
ID - Number
Space - Text
Bookings - List - REF_ROWS("Bookings", "Space")
Table: Bookings
Columns:
Booking ID - Number
Space - Ref Spaces
Start DateTime - Date
End DateTime - Date
Solved! Go to Solution.
You can't just add custom things into the filter toolbar, it only displays columns from that table that are visible and marked as searchable.
You should start a new post.
I make a filter [End DateTime]>=TODAY() to know which quotas are active
You mean with a Slice?
But I would like to have a view that shows the quotas that are expired and do not have an active quota.
How about just create a Slice with an opposing formula, [End DateTime] < TODAY()
I have thought about getting the last cell created in "End DateTime" but I can't get it to work.
I don't understand what the "last cell created in [End DateTime]" would do for you, or even what exactly you mean by that.
First of all, thank you for your response. The operation of the application is as follows: There is a table "Spaces" that contains the basic information about the customers (name, phone, email etc). I have another table "Bookings" in which I register the fees (each payment is a different fee). So if a person has contracted the monthly rate, after 6 months he/she has 6 installments.
For the specific functionality of the program it is necessary to block the access cards on the 1st of each month to people who have not renewed their quota.
With the formula: [End DateTime] < TODAY() shows me the active quotas.
What I need is to see the customers who have expired quotas but do not have another active quota.
Thanks again
Ahh, I gotcha. So, let's create a Slice on the "Spaces" table, with a formula like this:
AND(
ISBLANK( SELECT(
[Bookings][ID] ,
[End DateTime] >= TODAY()
) )
,
ISNOTBLANK( [Bookings] )
)
That Slice will show a list of all "Spaces"/customers (not a great naming schema you've got there I'd say...) that have an expired quota
Thank you very much for your reply it works perfect.
Would there be any way to group, or use a filter from the magnifying glass, in this view the customer files by date ?
Not sure what you mean.
Could "End DateTime" be added in one of the search filters?
To search by months or specific dates
You can't just add custom things into the filter toolbar, it only displays columns from that table that are visible and marked as searchable.
I am trying to get only the chips with the quotas of the last 30 days but I get older quotas as well. Am I putting something wrong in the formula?
AND(
ISBLANK( SELECT(
[Bookings][Space],
[End DateTime] >= TODAY()
AND [End DateTime] <= TODAY() - 30
)),
ISNOTBLANK([Bookings])
)
For me the ideal would be to be able to get in a column of my Space table, the date of the last quota created from End Datetime for each user.
I have spent many hours to find a solution but it is impossible.
Thanks in advance
You're using AND wrong.
I can't find where the error is.
Can you help me? Thanks in advance
.
First of all thank you for your help.
1
I had made a mistake. I am trying to make it to sort by the "End DateTime" date, or at least have a GoogleSheet where I can sort by date. how could I sort them?
2. My main goal, and what would be ideal for the project, is to have a column in the "Spaces" table (it is where I keep the information of the clients' cards) in which the date of their last booking is stored. Taking into account that each person can have several bookings, I need to extract the "End DateTime" of their last booking. I don't know if I have explained well.
Is there a way to do it?
You should start a new post.
You are right, I have already created it, thanks for your help.
I leave the link in case anyone is interested.
User | Count |
---|---|
41 | |
25 | |
25 | |
16 | |
12 |