2 Replies Latest reply on Dec 15, 2016 9:34 AM by Doug Stanley

    Help modifying YoY table calc

    Doug Stanley

      I've created a dual axis chart that compares forecasted revenues against historic revenues.

       

      The most recent period for the historic data is Oct. FY17. The forecast goes out one year to Oct FY18.

       

      The YoY growth table calc in Tableau, then, draws a chart that looks like this:

       

       

      I'm trying to modify the table calc to suppress the YoY comparison for the historic data after Oct. FY17 so that I don't get -100% YoY growth.

       

      I've tried several variations of this calculation, but can't quite get it to work right:

       

      IF MIN(DATEPART('year',[Fiscal Month]))<2018

      AND MAX(DATEPART('month',[Fiscal Month]))<11

      THEN (ZN(SUM([Revenue Observed])) - LOOKUP(ZN(SUM([Revenue Observed])), -1)) / ABS(LOOKUP(ZN(SUM([Revenue Observed])), -1))

      ELSE NULL

      END

       

      The result of that attempt is November and December are removed from every year. What I'm trying to do is tell Tableau to stop computing YoY growth after Oct. FY 2017.

       

      Apologies for the lack of a packaged workbook. Appreciate any help offered.

        • 1. Re: Help modifying YoY table calc
          Joe Oppelt

          I could be more specific if I had an actual workbook to try out things.


          But the first thing I would try is in the YOY growth calc itself, I would wrap the whole thing in another IF layer that did something like this:

           

          IF [whatever date field you are using] < (whatever date range you want this to stop at) THEN

          ... do your stuff

          END

           

          That approach should leave nulls beyond your limit.  (Not specifying an ELSE will result in a null when the implied else condition is hit.)

          • 2. Re: Help modifying YoY table calc
            Doug Stanley

            Thanks, Joe Oppelt You're always among the first to help when I need it, and I appreciate it.

             

            I couldn't quite get there with your suggestion for modifying my calc, but I found a two-step solution.

             

            There may be a more elegant way, but it works.

             

            Posted for those who might find this thread later:

             

             

            Step 1:

             

            IF ISNULL([Historic data])=TRUE

            THEN 0

            ELSE 1

            END

             

            Step 2:

            IF sum([Step 1])>0

            THEN [Historic data YoY growth]

            ELSE NULL

            END