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

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

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)

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)

bjackson1990

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