I need a beast mode that provides 2025 Sales % VS 2025 targets

CK_16
CK_16 Member
in Beast Mode

2025 targets are based on 2024 sales up to todays date multiplied by a growth rate. For product category 1 the growth rate is 5.5% and for product category 2 it is 14.5%. Sales are recorded in the column 'Extended Amount' and date is determined by column 'TRX Date'. I currently have the below Beast mode but it is returning an error. Could someone please advise where I might be going wrong.

(CASE when YEAR(trx_date)='2025' then extended_amount else 0 End) /
CASE
WHEN Product Category LIKE 'Product 1%' THEN
sum((CASE WHEN ((Year(trx_date) = Year(DATE_SUB(Current_Date(),INTERVAL 1 Year))) AND (DAYOFYEAR(trx_date) <= DAYOFYEAR(Current_Date()))) THEN extended_amount * 1.055 ELSE 0 END ))
ELSE sum((CASE WHEN ((Year(trx_date) = Year(DATE_SUB(Current_Date(),INTERVAL 1 Year))) AND (DAYOFYEAR(trx_date) <= DAYOFYEAR(Current_Date()))) THEN extended_amount * 1.145 ELSE 0 END ))
END

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @CK_16 The error can be resolved by rearranging the calculation so that the numerator and denominator of your beast mode each contain a single case statement within a sum like below. 

    sum(case when year(`trx_date`) = 2025 then `extended_amount` else 0 end)
/ 
sum(
  case 
    when `Product Category` like 'Product 1%' 
       and year(`trx_date`) = year(current_date()) - 1 
       and dayofyear(`trx_date`) ⇐ dayofyear(current_date())
      then `extended_amount`*1.055
    when year(`trx_date`) = year(current_date()) - 1 
       and dayofyear(`trx_date`) ⇐ dayofyear(current_date())
      then `extended_amount`*1.145
    else 0 
  end)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @CK_16 The error can be resolved by rearranging the calculation so that the numerator and denominator of your beast mode each contain a single case statement within a sum like below. 

    sum(case when year(`trx_date`) = 2025 then `extended_amount` else 0 end)
/ 
sum(
  case 
    when `Product Category` like 'Product 1%' 
       and year(`trx_date`) = year(current_date()) - 1 
       and dayofyear(`trx_date`) ⇐ dayofyear(current_date())
      then `extended_amount`*1.055
    when year(`trx_date`) = year(current_date()) - 1 
       and dayofyear(`trx_date`) ⇐ dayofyear(current_date())
      then `extended_amount`*1.145
    else 0 
  end)
  • CK_16
    CK_16 Member

    That works now thank you so much for your help Michelle!

Categories