Using a 'Created Date' as a series Parameter

Reply
White Belt

Re: Using a 'Created Date' as a series Parameter

hey thanks for all the time you're putting into this! 

 

i still got a syntax error on your update; i took a screenshot and posted it below just in case the formatting and color would help you troubleshoot it. good call on the other formula i set up, i believe you're right on the Q1 full week counting because my numbers were a bit higher in Q1 week 1 than they should have been.

Highlighted
Black Belt

Re: Using a 'Created Date' as a series Parameter

Sorry, there was an extra comma:

 

CASE 
    WHEN MONTH(`Created Date`) IN ('2','3','4') then 
FLOOR(DATEDIFF(`Created Date`, STR_TO_DATE(CONCAT('2-1-',YEAR(`Created Date`)),'%m-%d-%Y'))/7)+1
    WHEN MONTH(`Created Date`) IN ('5','6','7') then
FLOOR(DATEDIFF(`Created Date`,STR_TO_DATE(CONCAT('5-1-',YEAR(`Created Date`)),'%m-%d-%Y'))/7)+1
    WHEN MONTH(`Created Date`) IN ('8','9','10') then
FLOOR(DATEDIFF(`Created Date`,STR_TO_DATE(CONCAT('8-1-',YEAR(`Created Date`)),'%m-%d-%Y'))/7)+1
    WHEN MONTH(`Created Date`) IN ('11','12') then
FLOOR(DATEDIFF(`Created Date`,STR_TO_DATE(CONCAT('11-1-',YEAR(`Created Date`)),'%m-%d-%Y'))/7)+1
    WHEN MONTH(`Created Date`) IN ('1') then
FLOOR(DATEDIFF(`Created Date`,STR_TO_DATE(CONCAT('11-1-',YEAR(`Created Date`)-1),'%m-%d-%Y'))/7)+1
END

I also added a +1 to each line so that you don't get weeks 0-12 but instead get 1-13


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!