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

bjackson1990 · 2024-08-12T17:49:29+00:00

There was an error rendering this rich post.

bjackson1990

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!!!

Find more posts tagged with

Accepted answers

MichelleH

@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)

All comments

MichelleH

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)

bjackson1990

That can't work because Magic ETL is not an option in my setup since this is a Federated Dataset