Using a 'Created Date' as a series Parameter

Reply
Highlighted
White Belt

Using a 'Created Date' as a series Parameter

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!


Accepted Solutions
Black Belt

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

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

______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

All Replies
Black Belt

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

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

______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Black Belt

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

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

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

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

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)

Black Belt

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

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

______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

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

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?

 

White Belt

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

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

Black Belt

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

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. 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Black Belt

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

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

______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
White Belt

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

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. 

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!