8 Replies Latest reply on Feb 22, 2016 9:00 PM by angelina.fleckenstein

    Calculate the difference of a ratio number between months that are in consecutive years

    angelina.fleckenstein

      Hello,

      I'm very new to Tableau and would really appreciate some help with what I'm trying to do. I need to calculate the difference of a ratio number between months that are in consecutive years. Then the difference should be represented in a shape (up,down arrow, side arrow).

      My data set has columns: Year, building id, buildingname, month number, month description, totaltargetempl, totalempl

      For each month, building and year, the number represented is sum(totaltargetempl) / sum(totalempl)

       

      The viz needs to display only 2 most recent years and 2 or 3 most recent months.

      The difference should be calculated for month across the 2 years - the number should be displayed and a visual should represent if the number is up or down compared to previous year's month.

      Example: August 2015 has 0.97 = 97%, August 2014 has 0.96 = 96%. the difference is 1% so the change should be displayed as up arrow.

      If current month is February, data needs to be displayed for February 2016, February 2015, January 2016, January 2015

      Here is how i'm trying to represent it.

       

      visual.jpg

      I tried creating multiple calculated fields and table calculations, using last(), lookup(), and other functions, but cannot seem to get it right.

      How do I return the correct values for the current year/month and previous year/month? Tried multiple partition/attribute combinations, but can't get it right.

      How do I add a separate column just for the shape? when I create a KPI and add to color and shape, it gets added in the same cell as the % number displayed.

      I have attached  .twbx as an example data set with the fields I have. I removed all the gazzilion calculated fields I created and played with so there is no clutter.

      Thank you very much