3 Replies Latest reply on Apr 10, 2018 3:42 AM by Zhouyi Zhang

    Find YTD sales and Previous year YTD sales by calculated fields (without parameters)

    Jyothsana PJ

      Hi everyone,

       

      I have sales data for 3 years starting from Jan' 16 to Mar'18. I would like to calculate YTD and previous year YTD (marking as PYTD) sales by using calculated fields and without parameters.

      For example, YTD sales will be calculated for sum of sales from Jan'18 to Mar'18 and PYTD sales will be sum of sales from Jan'17 to Mar'17.

      I have got the following formulas for both YTD and PYTD which contains a today function used in it and it throws an error in results. I tried to use MAX(Date) function to find latest date available in data, but it throws an error message saying "cannot mix aggregate and non-aggregate arguments with this function"

       

      YTD 

      IF

      DATEDIFF('year',[Date],TODAY())= 0

      AND

      [Date] <= DATETRUNC('day',([Date]))

      THEN [Value] END

       

       

      PTYD

      IF

      (

      YEAR([Date]) = YEAR(TODAY())-1

      AND

      (MONTH([Date]) <= MONTH(TODAY())

      AND

      DAY([Date]) <= DAY(TODAY()))

      )

      THEN [Value]

      END

       

      The error facing in PYTD formula, MONTH(TODAY()) part looks for today's date and it calculates sales for previous year's 4 months instead of 3 months. Sales is available up to Mar'18.

      Ideally, when three months sales are calculated from current year, the sales for same three months should be calculated from previous year.

      Also, when a new month sales get added to the source, it should be applicable to new month from current year and corresponding previous year.

       

      Attached a packaged workbook (Tableau desktop version 10.5) having a sheet calculating YTD and PYTD.

       

      Can someone sort this out immediately.

      Any help would be great.

       

      Thanks,

      Jyothsana