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

bjackson1990
bjackson1990 Member
in Beast Mode

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

  • MichelleH
    MichelleH Coach
    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)

Answers

  • MichelleH
    MichelleH Coach
    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)
  • bjackson1990
    bjackson1990 Member

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

Categories