0 Replies Latest reply on Oct 31, 2013 10:52 AM by Tom Jones

    MTD & YTD Calculations, backlog for month and remainder of year

    Tom Jones

      I want to create a table (and later a chart) to show:

      ·         Jobs Completed YTD,

      ·         Jobs Completed MTD,

      ·         Jobs Scheduled for Today

      ·         Jobs Scheduled for the remainder of the fiscal month

      ·         Jobs Scheduled for the remainder of the fiscal year

      I

       

      I'

      My data (simplified version) looks something like this – I translated the original dates (in mm/dd/yyyy format) to my company’s fiscal calendar – fiscal year, fiscal month, and selling day (20, 20, 25 pattern).

      My data is formatted like (several fields omitted)

      CaseID

      Date Scheduled
      in System

      Date to be
      Performed

      1

      12/31/2012

      1/15/2013

      2

      12/31/2012

      1/8/2013

      2

      1/2/2013

      2/1/2013

      3

      1/4/2013

      1/10/2013

      3

      1/4/2013

      3/1/2013

      4

      1/5/2013

      2/4/2013

      4

      1/10/2013

      1/14/2013

      5

      1/14/2013

      1/17/2013

      5

      1/14/2013

      1/25/2013

      6

      1/17/2013

      1/22/2013

      6

      1/18/2013

      2/15/2013

      7

      1/22/2013

      3/4/2013

      7

      1/25/2013

      2/4/2013

      8

      1/28/2013

      1/31/2013

      8

      1/31/2013

      2/7/2013

      9

      2/1/2013

      3/4/2013

      10

      2/4/2013

      2/22/2013

      11

      2/7/2013

      2/28/2013

       

      And I need to produce a table like:

      Fiscal
      Year

      Fiscal
      Month

      Selling
      Day

       

      Jobs
      YTD

      Jobs MTD

      Jobs on
      This Date

      Jobs
      Rest of Month

      Jobs
      Rest of Year

      2013

      1

      1

       

      0

      0

      0

      2

      2

      2013

      1

      2

       

      0

      0

      0

      2

      2

      2013

      1

      3

       

      0

      0

      0

      2

      3

      2013

      1

      4

       

      0

      0

      0

      2

      3

      2013

      1

      5

       

      0

      0

      0

      3

      6

      2013

      1

      6

       

      0

      0

      0

      3

      6

      2013

      1

      7

       

      0

      0

      1

      2

      5

      2013

      1

      8

       

      1

      1

      0

      2

      5

       

      I’ve solved the conversion from Calendar dates to Fiscal / Selling dates.  However, I can’t solve all the calculations.

      For Jobs YTD, I can use a table calculation (running total), and should be able to use running total within a pane for Jobs MTD.  The “Jobs on this Date” is straightforward as well.

      However, I’m struggling with the ‘Jobs for the Rest of the Month” and Jobs for the Rest of the Year” within Tableau since I need to compare not only the ‘Perform Date’ to the Date, but also the Scheduled Date – (need to know that this case was scheduled prior to the fiscal date).

      I’ve attached a spreadsheet that should provide more detail.

       

      Thanks!