Need help with beast modes to show yesterday's invoice total amount, and same day last year
I'm trying to create 2 beast modes to show yesterday's Total Invoice Amount and same day last year's Total Invoice Amount. For example, 10/27/2022, and 10/28/21
Yesterday: case when year(`INVOICE_DATE`) = year(CURDATE()) and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT` end --- This formula works
Same day last year: case when year(`INVOICE_DATE`)=YEAR(CURDATE()) -1 and YEAR(INVOICE_DATE) = MONTH(CURDATE()) then INVOICE_TOTAL_AMOUNT
Same day last year's formula/code does not work.
Please help me. Thank you so much
Answers
-
Watch out for your second portion of the evaluation...easy typo but you're looking at YEAR() vs MONTH() so it's not going to yield any results.
Same day last year: case when year(`INVOICE_DATE`)=YEAR(CURDATE()) -1 and YEAR(INVOICE_DATE) = MONTH(CURDATE()) then
INVOICE_TOTAL_AMOUNT
^^^ instead of year(invoice_date) I think you'd want month(invoice_date)
Your formulas don't really sync up with how you're describing the need. The "yesterday" beast shown is more like "current month to-date, including today". Yesterday would be something along the lines of case when INVOICE_DATE=CURDATE()-1 then INVOICE_TOTAL_AMOUNT.
When talking about yesterday year over year you can do it many ways. Keep leap years in mind so you're ready for 2024. Using DAYOFYEAR() is really easy and will work fine till 2024, then it's not a perfect calendar day match for most months. Or could use something along the lines of WHEN DATE_SUB(CURDATE(),INTERVAL 366 DAY) = INVOICE_DATE THEN INVOICE_TOTAL_AMOUNT. Or something that looks at DAY/MONTH/YEAR-1 matches. So many ways... :)
1 -
Hi Bilobi, thank you for your comment. Let me explain again so you can understand better. My grammar is bad. Sorry. What I'm trying to do is I'm creating a daily ecommerce report, that shows previous day's report vs same day previous year's report. This sentence is really confusing. For example, yesterday Thursday was 10/27/22 and last year was Thursday 10/28/21 (+1 day). Because I selected the date range as "Yesterday" (see top right corner), below picture is showing invoice total amount for yesterday. What i want to do is i want to create a beast mode that can show invoice total amount same day last year (which is 10/28/21), how can i do that? Please help
Formula for yesterday Invoice total amount is case when year(`INVOICE_DATE`) = year(CURDATE()) and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT`end
As you can see, this yesterday beast mode matches the invoice_total_amount ( I used InvoiceTotal_Amount as Measures and I sum it. The numbers match
Formula for same day last year Total:
case when YEAR(`INVOICE_DATE`) =YEAR(CURDATE()) -1 and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT` END
Same day last year is not showing any numbers. I don't know what to do.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive