Day on Day Change Issue
Hi,
I am trying to calculate the day on day changes of campaign activities (i.e. Sends, Receipts). The problem experienced is restricting the unique counts to one day because the code I've been using includes some records of the previous day. Depending on the time of viewing the domo cards, it takes the record of 24hrs from the time of viewing (which is not necessarily 24 hrs of the entire day). I've been using date_format, specifically:
(COUNT(DISTINCT
CASE
WHEN DATE_FORMAT(`campaign_schedule_date_local`, '%Y%m%d') = DATE_FORMAT(DATE_SUB(CURDATE('%Y%m%d'), INTERVAL 0 Day),'%y%m%d') AND `media_booking_type` <> 'Sample' AND (`campaign_status` = 'Scheduled' OR `campaign_status` = 'Sent')
THEN `campaign_id`
END))
I want to see if using time_format is able to restrict the timeframe for representing daily changes to the exact day (i.e. Up until 23:59:59) but have not been able to see any values return from the code when changing from date_format to time_format. Could you please advise?
Any help is appreciated. Cheers
Best Answers
-
Hey, sorry if not made clear.
At the moment, depending on when the user views the cards, some of the records that make up the overall DoD value are from the previous day and some from the current day. So for DoD, I want to take the records up to '23:59:59' for each day, creating an internal day view.
12 -
Okay that helps clear it up. What datatype is your column "campaign_schedule_date_local" stored as? DateTime?
The way we tackle Period Over Period calculations is like this:
- Set the date range to the previous two periods (days in our case).
- You'll need 2 calculations: 1 for the current period (day), and one for the previous period (day).
- Depending on if it's year, month, or day, you'll use the appropriate function to test the day values. I.e. YEAR(), MONTH(), or DAY(). These return numerical values, so 8 for August as an example.
- Then you simply subtract the previous period's values from the current period's values.
As an simplified example, the current period's calculation could look like this:
CASE
WHEN
DAY(campaign_schedule_date_local) = DAY(CURDATE())
THEN
`campaign_id`
ENDWhich will return all `campaign_id`s that have a `campaign_schedule_date_local` equal to the current date's value.
The previous day's value could then be:
CASE
WHEN
DAY(campaign_schedule_date_local) = DAY(CURDATE()) - 1
THEN
`campaign_id`
ENDAs a huge caveat though, you'll have to consider UTC timezone offsets which can skew your results. This link fully details it and proposes some solutions: https://knowledge.domo.com/?cid=timezoneissues
The above might not answer your question exactly, but it should be a good start.
0 -
Thanks dthierjung for the write-up. I'll have a look tomorrow to see if using the previous day calculation to get the internal day value works for me, and report any futher updates that I may notice. The dataset 'campaign_schedule_date_local' just has date.
12
Answers
-
So I'm a little confused after reading your question.
You want to calculate day over day changes for campaign activities.
You have records from previous days but these are skewing your numbers?
When a user views the card, you want them to see the current day's (up until 23:59:59) campaign numbers compared to the entire previous day's numbers? Or do you want them to see the current day's numbers compared to the previous day at the same time of viewing?
Let me know if I misread that.
0 -
Hey, sorry if not made clear.
At the moment, depending on when the user views the cards, some of the records that make up the overall DoD value are from the previous day and some from the current day. So for DoD, I want to take the records up to '23:59:59' for each day, creating an internal day view.
12 -
Okay that helps clear it up. What datatype is your column "campaign_schedule_date_local" stored as? DateTime?
The way we tackle Period Over Period calculations is like this:
- Set the date range to the previous two periods (days in our case).
- You'll need 2 calculations: 1 for the current period (day), and one for the previous period (day).
- Depending on if it's year, month, or day, you'll use the appropriate function to test the day values. I.e. YEAR(), MONTH(), or DAY(). These return numerical values, so 8 for August as an example.
- Then you simply subtract the previous period's values from the current period's values.
As an simplified example, the current period's calculation could look like this:
CASE
WHEN
DAY(campaign_schedule_date_local) = DAY(CURDATE())
THEN
`campaign_id`
ENDWhich will return all `campaign_id`s that have a `campaign_schedule_date_local` equal to the current date's value.
The previous day's value could then be:
CASE
WHEN
DAY(campaign_schedule_date_local) = DAY(CURDATE()) - 1
THEN
`campaign_id`
ENDAs a huge caveat though, you'll have to consider UTC timezone offsets which can skew your results. This link fully details it and proposes some solutions: https://knowledge.domo.com/?cid=timezoneissues
The above might not answer your question exactly, but it should be a good start.
0 -
Thanks dthierjung for the write-up. I'll have a look tomorrow to see if using the previous day calculation to get the internal day value works for me, and report any futher updates that I may notice. The dataset 'campaign_schedule_date_local' just has date.
12
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive