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

       

      Help?