Using a 'Created Date' as a series Parameter

MLAstuto · August 2019

Hey everyone,

I'm trying to find out if it's possible to use the created date of thousands of leads, and group them into 'Quarter 1', 'Quarter 2', etc. etc.; and use that grouping as a series to group data by?

I have a 'Created Date' field which is in a standard long date format. my Fiscal year starts on 2/1, and i already have Domo set up to account for my fiscal year. so my chart dates are set up to view the current fiscal year. i've set up in Beastmode a label for the weeks of the year, so i have 1 - 52 on my x axis.. but now i'm stuck trying to figure out how to group that date field as a "Quarter 1" "FY20 Q1" "FY20 Q2" "FY20 Q3" "FY20 Q4". so that i can use that field as a 'series' to group by, like the picture from my tableau attached.

hope someone can help, thanks!

ST_Superman · August 2019

I think something like this should work:

CASE 
    WHEN MONTH(`Created Date`) IN ('2','3','4') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','1') 
    WHEN MONTH(`Created Date`) IN ('5','6','7') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','2')
    WHEN MONTH(`Created Date`) IN ('8','9','10') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','3')
    WHEN MONTH(`Created Date`) IN ('11','12') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','4')
    WHEN MONTH(`Created Date`) IN ('1') then concat('FY',RIGHT(YEAR(`Created Date`)-1,2),' Q','4')
END

ST_Superman · August 2019

I think something like this should work:

CASE 
    WHEN MONTH(`Created Date`) IN ('2','3','4') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','1') 
    WHEN MONTH(`Created Date`) IN ('5','6','7') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','2')
    WHEN MONTH(`Created Date`) IN ('8','9','10') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','3')
    WHEN MONTH(`Created Date`) IN ('11','12') then concat('FY',RIGHT(YEAR(`Created Date`),2),' Q','4')
    WHEN MONTH(`Created Date`) IN ('1') then concat('FY',RIGHT(YEAR(`Created Date`)-1,2),' Q','4')
END

ST_Superman · August 2019

You would still need to calculate the `Week of Quarter` field though...

MLAstuto · August 2019

My friend this worked beautifully! at a glance it looks right!

but you are correct what you said below, i think i did mess up the week of quarter field;

here's what i used in beastmode [ WEEKOFYEAR(`Created Date`) ].. but this gave me the actual number value associated with the weeks, starting at week 5(because fiscal year starts on 2/1) through week 32 (because that's where we are now on 8/9).. but what i need is to really just Index() the weeks of the year as 1 - 52; but Index() isn't a valid formula field with Domo. this way Q1 through Q4 start at week 1;

any advice there?

here's the screenshot of where i'm at now; (attached)

ST_Superman · August 2019

Try this for the week:

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)
    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)
    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)
    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)
    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)
END

MLAstuto · August 2019

Hey this one returned a syntax error; i tried to troubleshoot it but couldn't figure it out;

i wonder if there's a way to tackle this 1 week at a time (and maybe this is the hard way) but for example, we know the week of the year starts on week 5 (2/1/2019 for my fiscal year). and we know quarter 2 starts on week 18, quarter 3 on week 31, and quarter 4 on week 44;

couldn't we say something like; If weekofyear = 5,18,31,44 then 'week 1', else if weekofyear - 6,19,32,45 then 'week 2' etc. etc. etc.

then i should end up with week 1 - 13 on my x axis no matter what the date range is; would something like that work?

MLAstuto · August 2019

OK i got the week part; here it is in case anyone needs; just remember my fiscal starts on week 5, so if you start on jan 1, then add 4 weeks lines to the beginning and drop 4 off the end and make a few adjustments;

i also attached what the final product looks like; a quarter over quarter by week comparison;

CASE

WHEN WEEKOFYEAR(`Created Date`) IN ('5','18','31','44') then 'W1'

WHEN WEEKOFYEAR(`Created Date`) IN ('6','19','32','45') then 'W2'

WHEN WEEKOFYEAR(`Created Date`) IN ('7','20','33','46') then 'W3'

WHEN WEEKOFYEAR(`Created Date`) IN ('8','21','34','47') then 'W4'

WHEN WEEKOFYEAR(`Created Date`) IN ('9','22','35','48') then 'W5'

WHEN WEEKOFYEAR(`Created Date`) IN ('10','23','36','49') then 'W6'

WHEN WEEKOFYEAR(`Created Date`) IN ('11','24','37','50') then 'W7'

WHEN WEEKOFYEAR(`Created Date`) IN ('12','25','38','51') then 'W8'

WHEN WEEKOFYEAR(`Created Date`) IN ('13','26','39','52') then 'W9'

WHEN WEEKOFYEAR(`Created Date`) IN ('14','27','26','53') then 'W10'

WHEN WEEKOFYEAR(`Created Date`) IN ('1','14','27','40') then 'W11'

WHEN WEEKOFYEAR(`Created Date`) IN ('2','15','28','41') then 'W12'

WHEN WEEKOFYEAR(`Created Date`) IN ('3','16','29','42') then 'W13'

WHEN WEEKOFYEAR(`Created Date`) IN ('4','17','30','43') then 'W14'

END

ST_Superman · August 2019

The only issue I see here is that you won’t always have a full week at the start or end of a quarter. For example, if quarter 2 starts on May 1... that is a Wednesday. So your formula will only include the first four days as part of that week. It is actually going to count that Sunday, Monday and Tuesday (April 28,29,30) as week 1 for quarter 1.

ST_Superman · August 2019

I guess I shouldn't try to post SQL code from my iPad. It seems that iPad's like to change single quotes to other characters. Try this as the week of quarter:

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)
    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)
    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)
    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)
    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)
END

MLAstuto · August 2019

Interesting, i hadn't thought of that, and also did notice that my Q1 was slightly higher than my source of truth in salesforce;.. let me try the updated formula you posted below.

MLAstuto · August 2019

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.

ST_Superman · August 2019

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

Using a 'Created Date' as a series Parameter

Best Answer

Answers

Categories