1 Reply Latest reply on Feb 21, 2012 8:39 PM by Vincent Romeo

    YTD Flag Calculated Field When Using Fiscal Year

    . Ew6050

      Good Morning -

       

      We make extensive use of a calcuated field called "YTD Flag" which has the following logic:

       

      If

      DATEPART('dayofyear',[Adj_Invoice_Date]) <= DATEPART('dayofyear',TODAY())

      then "TRUE"

      else "NULL"

      end

       

      That way if I set this flag to True via a quickfilter it only includes sales we have made in any year up until this point in the year for this year and any prior years. In essence it allows me to do apples-to-apples comparisons going back several years.

       

      However, we have one product line we sell where the supplier operates on a fiscal year ending October 31st and I am struggling to find a way to create a field - call it "FY YTD Flag" - which is like the above calculated field but it will include all sales from the beginning of the fiscal year up to today for each fiscal year.

       

      Any insight would be greatly appreciated.

       

      Thanks,

       

      Matt

        • 1. Re: YTD Flag Calculated Field When Using Fiscal Year
          Vincent Romeo

          There may be an easier way to solve this, but I created a sample workbook on Tableau public that I believe addresses your issue.  I built the solution in steps--of course, you could combine the 3 calculated fields into one and do away with the parameter, but I was trying to make the solution as generic as possible.

          Param: YearEnd (string - Month Day of fiscal year end, in this case "Oct 31")

          Calculated Fields:

          Inv_DayOfFY

           

          if date([YearEnd]+','+str(year([Inv Date])))<[Inv Date]

          then DATEDIFF('day',date([YearEnd]+','+str(year([Inv Date]))),[Inv Date])

          else DATEDIFF('day',date([YearEnd]+','+str(year([Inv Date])-1)),[Inv Date])

          end

           

          Today_DayOfFY

          if date([YearEnd]+','+str(year(TODAY())))<today()

          then DATEDIFF('day',date([YearEnd]+','+str(year(today()))),today())

          else DATEDIFF('day',date([YearEnd]+','+str(year(today())-1)),today())

          end

           

          YTD flag alt

          If

          [Inv_DayOfFY]<=[Today_DayOfFY]

          then "TRUE"

          else "NULL"

          end

           

          To calculate an invoice's day of fiscal year, we first check to see if the YearEnd (which is just Month & Day) combined w/ the year of the current invoice is less than the date of the invoice, and, if so, we do a datediff between those 2.  Otherwise we use the prior year appended to the YearEnd and datediff with the invoice date.  The same logic is applied to today's date.  Then we compare the two values to get our new YTD flag.

           

          Hope this helps!

          -Vince