1 Reply Latest reply on Jun 6, 2018 2:33 AM by Mavis Liu

    var vs previous year

    Emmanouil Paterakis

      Hi all,

       

      I have a problem calculating the variance vs previous year. I have a quick table calculation which calculates the % of total.

      When I am trying to calculate the variance vs previous I am getting incorrect numbers. My first calculation is table down and the variance I

      am trying to get I am using table across.

      Screenshot (21).pngScreenshot (22).png

       

      For example for month January I would like to get a 0% but instead I got 1.6

       

      The formula I use for variance is that.

      ((ZN(([SoM Brands])) - LOOKUP(ZN(([SoM Brands])), -1)))*100

       

      Kr,

      Emmanouil

        • 1. Re: var vs previous year
          Mavis Liu

          Hi Emmanouil,

           

          Are you trying to get the variance between your % of totals?

           

          Please make your first first table calculation and add it into the data pane. Then create the second table calculation and also do the same thing.

           

          I have attached an example where I have created a calculations:

           

          % of Total:

          SUM([Sales]) / TOTAL(SUM([Sales]))

           

          and also

          % Variance Change

           

          (ZN([% of Total]) -

           

           

          LOOKUP(ZN( [% of Total]), -1))

           

          My second table calculation references the first one. So now when I bring it into the view and select edit table calc, it gives me the option to control the nested table calc:

           

          2018-06-06_10h29_55.png

           

          So now I'm able to tell tableau I want the % of Total to be calculated table down, and I want the % Variance Change to be calculated by table across.

           

          I end up getting this view:

           

          2018-06-06_10h30_54.png

           

          I've attached a workbook for more information.

           

          Thanks,

           

          Mavis