5 Replies Latest reply on Dec 8, 2017 12:47 PM by Shinichiro Murakami

    Cohort First Order by Month Across Multiple Fiscal Years

    Seth Nenstiel

      Hi all,


      I have been searching the forum for an answer to my problem.


      I have created cohorts of my customers by Year of First Order and have changed our Fiscal Year start to July, creating a calculated field First Order - Year:


      {FIXED [Customer Number] : min([Bill Date])}


      Screen Shot 2017-12-06 at 9.59.57 AM.png


      Great, I now have a beautiful cohort chart that shows me when the customers reorder across multiple fiscal years, with the FY starting in July.  Here’s how I’ve placed the pills:


      Rows: COUNTD([Customer Number])

      Columns: YEAR([Year of First Order])

      Colored by: YEAR([Year of First Order])


      Screen Shot 2017-12-06 at 9.59.15 AM.png


      Life is good up until… I get asked to see when a distinct customer places their first order in the fiscal year by months. We want to know which month is the busiest and spot trends year over year.


      Okay, I think no problem –


      Create a calculated field - First Order - Month


      {FIXED [Customer Number] : DATEPART(‘month’, MIN([Bill Date]), ‘Sunday’)}


      Screen Shot 2017-12-06 at 9.59.48 AM.png


      *Tableau added some other formatting code to my original formula when I display it as a discrete field.


      Boom, month of first order.


      Placing the pills:


      Rows: COUNTD([Customer Number])

      Columns: YEAR([Bill Date]), MONTH([First Order - Month])


      Screen Shot 2017-12-06 at 9.58.53 AM.png


      And if I want to see the different cohorts, placing YEAR([First Order - Year)] on the color selector on the marks card.


      Screen Shot 2017-12-06 at 9.59.06 AM.png


      Except, when the month is returned from the DATEPART is is without a year, so even though the [Bill Date] may be from last fiscal year (January through June on FY 2017), when plotted, these customers are showing up as FY 2018 first orders – the DATEPART function essentially is causing the customers order to be considered part of the calendar year instead of the FY.


      Screen Shot 2017-12-06 at 10.03.25 AM.png