Prior YTD Beast Mode Date Filter - Date based on MAX(Billing Date)

I'm having issue trying to generate a Prior Year to Date calculation based on the max of our billing date 1 year prior . We cannot use Current Date function since my dataset is not truly 'LIVE'…here is what I have so far:
IFNULL(
CASE
WHEN YEAR(CALDAYFORMAT
) = YEAR(DATE_ADD(CALDAYFORMAT
, INTERVAL -1 YEAR))
AND RIGHT( Calday
,4 ) <= RIGHT( MAX( Calday
) )
AND Customer Group A
= '5D'
THEN sum( Invoiced Sales LC
)
ELSE 0
END,
0
)
Calday field is text in 'YYYYMMDD' day format and CALDAYFORMAT is a traditional date
Help please!!!
Best Answer
-
@bjackson1990 I would suggest using a Group by tile in Magic ETL to find the maximum of CALDAYFORMAT and call it MAXDAY to represent the most recent date in your dataset. Then you can use the beast mode below to calculate prior year YTD:
sum(case when YEAR(`CALDAYFORMAT`) = YEAR(`MAXDAY`) - 1 and DAY(`CALDAYFORMAT`) <= DAY(`MAXDAY`) and `Customer Group A` = '5D' then `Invoiced Sales LC` else 0 end)
0
Answers
-
@bjackson1990 I would suggest using a Group by tile in Magic ETL to find the maximum of CALDAYFORMAT and call it MAXDAY to represent the most recent date in your dataset. Then you can use the beast mode below to calculate prior year YTD:
sum(case when YEAR(`CALDAYFORMAT`) = YEAR(`MAXDAY`) - 1 and DAY(`CALDAYFORMAT`) <= DAY(`MAXDAY`) and `Customer Group A` = '5D' then `Invoiced Sales LC` else 0 end)
0 -
That can't work because Magic ETL is not an option in my setup since this is a Federated Dataset
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 816 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 778 Automate
- 190 Apps
- 482 APIs & Domo Developer
- 83 Workflows
- 23 Code Engine
- 41 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 2 AI Projects and Models
- 18 Jupyter Workspaces
- 412 Distribute
- 120 Domo Everywhere
- 281 Scheduled Reports
- 11 Software Integrations
- 145 Manage
- 141 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive