Hi all,
I am having a data below:
I am creating an automation that send me a report of number of destinations appear in that month.
I would like to know what is the best formula for it with the following conditions:
For example:
January: 5 destinations (omit 1 duplicate row)
February: 1 destination
I have been trying ways that I could think of but it does not work.
Thank you in advance.
For the month, you will need another column called [Month]
or similar and calculate it based on the [Date]
.
Examples:
MONTH([DATE])
INDEX(
LIST(
"January",
"February",
"March",
"April"... /*You got the idea, make the 12 months*/
),
MONTH([DATE])
)
INDEX(
LIST(
"01-January",
"02-February",
"03-March",
"04-April"... /*You got the idea, make the 12 months*/
),
MONTH([DATE])
)
About counting unique values, there is this simple expression:
COUNT(
UNIQUE(
/*INSERT A LIST OF VALUES HERE*/
)
)
The list will vary from table to table. You could even use SELECT()
and it has a built in parameter to filter unique values.
Your count could end like this:
COUNT(
SELECT(
TABLENAME[DESTINATION],
/*SOME CONDITION TO TAKE THE VALUES RELATED TO THE NEW MONTH COLUMN*/,
0=0 /*A TRUE EXPRESSION TO JUST CONSIDER UNIQUE VALUES*/
)
)
We canโt help much if we donโt have a full explanation of what you have done and your full table schema
Hi Oscar,
Thank you for your reply.
MIA-BD
11:00MIA-BD
12:30MIA-BD
14:00BD-MIA
11:30BD-MIA
13:00BD-MIA
14:30
SERVICE: will be automatically calculated based on a chosen Destination
FOLIO NO.: guest booking ID
I have created 2 virtual columns MONTH: MONTH([DATE]) and YEAR: YEAR([DATE])
So I am creating a bot to send me an email every last day of every month with a report of numbers (COUNT) of destination that we operate that month.
for example:
On 1/1/2022, 2 bookings book for MIA-BD 11:00 and 3 bookings book for BD-MIA 11:30 โ 2 destinations in total
on 1/2/2022, 1 booking book for MIA-BD 11:00 and 1 booking book for BD-MIA 11:30 โ 2 destinations
Total destinations of January: 4
The following is COMPLETELY UNTESTED. Typos and bugs are likely. Use at your own risk.
Create a new table named (e.g.) Monthly bookings summary
with the following columns:
Date
Date
Destination
Text
Bookings
Date
FILTER(
"Bookings",
AND(
ISNOTBLANK([Date]).
([Date] = [_THISROW].[Date]).
OR(
ISBLANK([_THISROW].[Destination]),
([_THISROW].[Destination] = [Destination])
)
)
)
_ID
Text
TRIM([Date] & " " & [Destination])
Create an action named (e.g.) Delete this monthly summary booking
:
Monthly bookings summary
(from step 1)Data: Delete this row
Do not display
Create an action named (e.g.) Delete all monthly summary bookings
:
Monthly bookings summary
(from step 1)Data: execute an action on a set of rows
Monthly bookings summary
(from step 1)FILTER("Monthly bookings summary", TRUE)
Delete this monthly summary booking
(from step 2)Do not display
Create an action named (e.g.) Add this booking to monthly summary
:
Bookings
Data: add a new row to another table using values from this row
Monthly bookings summary
(from step 1)Date
: [Date]
Destination
: [Destination]
Do not display
AND(
ISNOTBLANK([Date]),
ISNOTBLANK([Destination]),
ISBLANK(
FILTER(
"Monthly bookings summary",
AND(
([_THISROW].[Date] = [Date]),
([_THISROW].[Destination] = [Destination])
)
)
)
)
Create an action named (e.g.) Add all bookings for this monthly summary
:
Monthly bookings summary
(from step 1)Data: execute an action on a set of rows
Bookings
FILTER(
"Bookings",
(EOMONTH(TODAY(), 0) = EOMONTH([Date], 0))
)
Add this booking to monthly summary
(from step 4)Do not display
Create an action named (e.g.) Add this date to monthly summary
:
Monthly booking summary
Data: add a new row to another table using values from this row
Monthly bookings summary
(from step 1)Date
: [Date]
Do not display
AND(
ISNOTBLANK([Date]),
ISNOTBLANK([Destination]),
ISBLANK(
FILTER(
"Monthly bookings summary",
AND(
([_THISROW].[Date] = [Date]),
ISBLANK([Destination])
)
)
)
)
Create an action named (e.g.) Add all dates for this monthly summary
:
Monthly bookings summary
(from step 1)Data: execute an action on a set of rows
Monthly bookings summary
(from step 1)FILTER("Monthly bookings summary", TRUE)
Add this date to monthly summary
(from step 6)Do not display
Create an action named (e.g.) Add this destination to monthly summary
:
Monthly booking summary
Data: add a new row to another table using values from this row
Monthly bookings summary
(from step 1)Destination
: [Destination]
Do not display
AND(
ISNOTBLANK([Date]),
ISNOTBLANK([Destination]),
ISBLANK(
FILTER(
"Monthly bookings summary",
AND(
ISBLANK([Date]),
([_THISROW].[Destination] = [Destination])
)
)
)
)
Create an action named (e.g.) Add all destinations for this monthly summary
:
Monthly bookings summary
(from step 1)Data: execute an action on a set of rows
Monthly bookings summary
(from step 1)FILTER("Monthly bookings summary", TRUE)
Add this destination to monthly summary
(from step ๐Do not display
Create an action named (e.g.) Prepare monthly bookings summary
:
Monthly bookings summary
(from step 1)Grouped: execute a sequence of actions
Delete all monthly summary bookings
(from step 3)Add all bookings for this monthly summary
(from step 5)Add all dates for this monthly summary
(from step 7)Add all destinations for this monthly summary
(from step 9)Do not display
Create a new bot or update an existing bot to perform the Prepare monthly bookings summary
action (from step 6) immediately before you generate your monthly summary report.
In your report template, include the following:
<<Start: ORDERBY(FILTER("Monthly bookings summary", ISBLANK([Destination])), [Date])>>
On <<[Date]>>, <<Start: ORDERBY(FILTER("Monthly bookings summary", ISBLANK([Date])), [Destination])>><<Start: FILTER("Monthly bookings summary", AND(([_THISROW-2].[Date] = [Date]), ([_THISROW-1].[Destination] = [Destination]), ISNOTBLANK([Bookings])))>><<COUNT([Bookings])>> booking(s) for <<[Destination]>>, <<End>><<End>><<COUNT(FILTER("Monthly bookings summary", ISBLANK([Date])))>> destination(s) total<<End>>
Total destinations for <<INDEX(LIST("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"), MONTH(TODAY()))>>: <<COUNT(FILTER("Monthly bookings summary", AND(ISNOTBLANK([Date]), ISNOTBLANK([Destination]))))>>
we should have a firework icon instead of just a heart for answers like this one
So I am thinking of the way to count number DESTINATION for each date and then sum them up. Is there a way to do so?
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |