POP WOW %change Beast mode Error

user19883
user19883 Member
edited March 2023 in Datasets
 Marketing Solutions
 20192020% Var
Wk 15030-100%
Wk 27452-100%
Wk 356954-91%
Wk 430283844%

 

Help my beast mode is not working for the % variance.  I keep getting error message or no result in my table

 

(
(SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE())

                 AND (WEEKOFYEAR(`Opp | Close Date *`)= WEEKOFYEAR(CURDATE()))
THEN SUM(CASE WHEN `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *`
     ELSE 0 END)
ELSE 0
END)
- SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1

                   AND (WEEKOFYEAR(`Opp | Close Date *`)= WEEKOFYEAR(CURDATE()))
             THEN SUM(CASE WHEN `ETL | Amount Type` = 'Booking' THEN `Opp | Split Total Amount *`
                 ELSE 0 END)
ELSE 0
END)
)
/ NULLIF(SUM(CASE WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1

    AND (WEEKOFYEAR(`Opp | Close Date *`)= WEEKOFYEAR(CURDATE()))
           THEN SUM(CASE WHEN `ETL | Amount Type` = 'Booking'  THEN `Opp | Split Total Amount *`
            ELSE 0  END)
      ELSE 0
END),0)
)

 

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    It looks like the issue is because you are trying to account for the week in your Beast Mode, when the rows in your table are already separated by week. You can actually use a much simpler calculation that only compares the year over year because each row of your table is already only includes data from the same week of the year. Try this:

    (
(SUM(CASE 
   WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) And `ETL | Amount Type` = 'Booking'
   THEN `Opp | Split Total Amount *`
     ELSE 0 END)
- 
(SUM(CASE 
   WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' 
   THEN `Opp | Split Total Amount *`
      ELSE 0 END)
)

/ 

(SUM(CASE 
   WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' 
   THEN `Opp | Split Total Amount *`
      ELSE 0 END)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    It looks like the issue is because you are trying to account for the week in your Beast Mode, when the rows in your table are already separated by week. You can actually use a much simpler calculation that only compares the year over year because each row of your table is already only includes data from the same week of the year. Try this:

    (
(SUM(CASE 
   WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) And `ETL | Amount Type` = 'Booking'
   THEN `Opp | Split Total Amount *`
     ELSE 0 END)
- 
(SUM(CASE 
   WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' 
   THEN `Opp | Split Total Amount *`
      ELSE 0 END)
)

/ 

(SUM(CASE 
   WHEN YEAR(`Opp | Close Date *`) = YEAR(CURDATE()) - 1 AND `ETL | Amount Type` = 'Booking' 
   THEN `Opp | Split Total Amount *`
      ELSE 0 END)
  • user19883
    user19883 Member

    Thank you for the revised formula! 

Categories