1 2 Previous Next 22 Replies Latest reply on Mar 28, 2018 5:52 AM by Anthony Griffiths

    Help Needed: Same Month Previous Year Calculation

    Anthony Griffiths

      Hi everyone

       

      I'm a bit stuck, I can't see what I'm doing wrong so any help will be appreciated.

       

      I have a chart which is displaying the Average Cost Per Job (Cost/No. of Jobs) which is split by Area.

       

      This works as intended.

       

      What I'm trying to add on is a dual axis shape to show the Average Cost Per Job for the same month in the previous year based off whichever month the user selects from the [Date] field (Single Select Only)

       

      Average Cost Per Job.PNG

       

      So to do this i'm using the following calculated field:

       

      Average Cost Per Job Previous:

       

      { FIXED [Area]:

      SUM(

      IF

      //[Date] = #01/04/2016#

      [Date] = DATE(DATEADD('month',-12,[Date]))

      THEN

      [Cost]

      END)

      /

      SUM(IF

      //[Date] = #01/04/2016#

      [Date] = DATE(DATEADD('month',-12,[Date]))

      THEN

      [No. of Jobs]

      END)

      }

       

      When I do this I get nulls (5 Nulls seen in image above), but, as you can see from the commented out lines, if I manually type in the date for April 2016 for example, it works exactly as intended.

       

      Average Cost Per Job Manual Date.PNG

       

      I've also tried the MAKEDATE function to work out the previous year but nothing seems to be working.

       

      I'm connected to an excel spreadsheet via an extract. Using Tableau v10.2

       

      Please help!

        1 2 Previous Next