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
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 791 Beast Mode
- 78 App Studio
- 43 Variables
- 753 Automate
- 187 Apps
- 478 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive