1 Reply Latest reply on Mar 18, 2016 4:51 AM by Carl Slifer

    Finding Total Number of Gains and Losses in Year Over Year Changes

    Josh Mahar

      So I've got some data for the total valuation (value of all the properties) of various cities in Washington across a 5 year spectrum. I created some table calcs to identify the year-over-year changes, some cities have gained value and some have lost value year to year. To help visualize the gains and losses, I also created a calc field that simply returns either "gain" or "loss" depending on if the city gained or lost value over the year.

       

      Now, where I'm struggling is, I want to now take that "gain or loss" calculation and identify the number of records in each for each year. So basically, how many cities gained in value from 2011 - 2012 and how many lost value. Basically, since it uses the YOY valuation aggregate data, it won't let me convert it to a dimension, which means that I can't figure out a way to divide my data based on this. I can create a nice table with each year that indicates a gain or loss, but I can't figure out any way to aggregate that information.

       

      Is there another way to go about doing this? I've attached the workbook here.

        • 1. Re: Finding Total Number of Gains and Losses in Year Over Year Changes
          Carl Slifer

          Hi Josh,

           

          Attached is a workbook. This is now called fun with table calcs, oh boy here it goes.

           

          I made a three calculations. 2 of these are near identical they count the losses and the gains. They do this using a window_sum() function.

          Now your table calculation to find out the percent change runs across your table. In terms of tableau speak it has partitioned (grouped) by city and addressed (ran along) the year. Hence each cities calculation is independent of one another.

          But I what I wanted to do is to take your calculations value and compare it to 0. If it was greater than 0 then I summed up all instances of this. But what I did was group by year and ran along the city. As such we would get the number of gains per year.

          But to do this and just get a single line we have to muck around a bit.

           

          I moved city to the details shelf and edited your calculation from table across (there was no table) to the correct partitioning and addressing. In this case we can say compute using year. At this point, yes things look funny. I then made another table calculation to use as a filter.

          FIRST()=0. What this will do will only keep the first line of data. It's a table calculation so it runs after all the other filters and calculations run, which is good for us. We can now take the WINDOW_SUM() of those that exceeded 0 and were below 0 and place these into the view.

           

          These are nested table calculations. As there's the % change that runs for each city going across the years and the sum of those that were above 0 that runs for each year running through all the cities. So we have to edit these table calcs to behave appropriately. Open them up and choose edit table calc. There is the option to choose inner calc (your % change) and the outer calc (my window function).

           

          These can be a bit heavy but do not despair! This is a pretty good place to start looking if you get stuck reading through my logic:The Vizioneer: Demystifying Nested Table Calcs in Tableau

           

          Cheers!

          Carl Slifer

          InterWorks

          1 of 1 people found this helpful