3 Replies Latest reply on Aug 29, 2013 8:51 AM by Jim Wahl

    Please help creating period over period and year over year comparison using attached excel sheet!

    Dheeraj Poojari

      Hi All,

       

      I have posted a query similar to this but this is more elaborate query. Please help me solving it. My experience in Tableau is "Rookie", so be patient with me.

       

      I have an excel file, as attached, with Year, Period, Group, Page and Page views. I need to do few things

       

      1) Have filter to select Group( comfortable doing it)

      2) Have filter to select Year( comfortable doing it)

      3) Have filter to select Period( comfortable doing it)

      4) Show Current Period Page Views( Not sure how) for relevant pages

      5) Show Previous Period Page views ( not sure how) for the current period page views based on selected period filter

      6) Show % difference ( Current/Previous-1)

      7) Show arrow marks for % diff

       

      I've scratched my head against various social sites and the tableau forum, downloading every single sample there is, but the problem is that I have a "Period" to filter/total by( Period is a organization decided time frame ).

       

       

      The Screen shot of the desired output and the excel file with the raw data is attached.

        • 1. Re: Please help creating period over period and year over year comparison using attached excel sheet!
          Jim Wahl

          Hi Dheeraj,

           

          Sorry it's taken a while for you to get a response to your question. I think it's an excellent example of table calcs and also an example that shows how you can avoid table calcs using parameters. It's also an good use case for Jonathan Drummey's excellent "multi-axis crosstab" technique.

           

          Which approach you take depends largely on how users will be interacting with the view. If you can rely on users to enter the period they're interested in viewing (with a warning when it's not the latest period), then the parameter approach is easier and faster. It also has the advantage of showing the rank, which I haven't yet achieved with the table calc approach.

           

          First, starting with the goal: You want three measure columns for each Page

          1. page views for the currently selected period,

          2. page view for the previous period,

          3. period-over-period (POP) change.

          KPIs_Tableau3.png

           

          Table Calc Approach with LAST()

          Whenever you find yourself saying, "I need the last ..." you're next thought might be a table calc using the LAST() function.

           

          Rather than jump right in with LAST(), it might be helpful to build up a table calc. Start by creating a calculated field that is the simplest table calc: Page Views Current Period =

          LOOKUP(SUM([Page Views]), 0)
          
          
          

           

          When entering the table calc, I like to specify the default compute using, which in this case is Period, since we want the values for each period (and will ultimately grab just the last value).

          2013-08-28 15-01-48.png

           

          Create the below view by double clicking Page to add it to the Rows shelf and double clicking the new field Page Views Current Period to add it to Text shelf. If you set the default compute using above, the page views pill will be red, since Period is not in the view (and, therefore, Tableau is not pulling the row level data from the data source). To fix this, drag Period to the Detail shelf to make the data available in the view without adding an additional pane or axis.

           

          Now Tableau is executing the formula (LOOKUP(SUM([Page Views]), 0) for each Period, and you'll therefore see the page view total for each period. You can hover over the values to see the Period for each mark. .

          2013-08-28 15-08-38.png

           

          Now what you really want is just the last period, which you can get by wrapping the function in a LAST(). NOTE: I removed the LOOKUP() function, since LAST() will make this a table calc and, therefore, LOKUP(SUM(x) 0) is equivalent to SUM(x)

          IF LAST() == 0 THEN
              SUM([Page Views])
          END
          
          

           

          2013-08-29 05-06-32.png

           

          When you hover over some of the marks you'll notice a problem: The calc is returning the last value for each partition, in this case Page, and some pages don't have a value for the last period. The LAST() value for page 28 above for example is P8.

           

          Finding the last period in the (filtered) data.

          To find the last period in the data, I used another table calc WINDOW_MAX() and set the addressing to both Page and Period. Since  Period is a string and P13 is < P2 (alphabetically), I first created an integer Period Number and then found the WINDOW_MAX() of this to find the Last Period.

          Period Number =

          INT(MID(Period, 2))
          
          

           

          Last Period =

          'P' + STR(WINDOW_MAX(MAX([Period Number])))
          
          

           

          When entering Last Period, again click on the Default Table Calculation. In the Compute using pull-down box, select Advanced and move both Page and Period to the right-had addressing side. (Ideally in that order, since addressing is hierarchical).

           

          To test this, you can add Last Period to the Detail shelf and hover over any of the marks. If the last period is not P13 for all values, double check that compute using is set to both Page and Period.

           

          Now is also a good time to right-click on Period and select Show Quick Filter. You'll notice that if you filter out P13, Last Period is P12, since filters are applied at the data source the Last Period table calc no longer "sees" the P13 rows.

           

          Finding Page Views in Last Period

          Now we can use Last Period as a conditional.

          Page Views Current Period =

          IF MIN([Period]) == [Last Period]
          THEN SUM([Page Views])
          END
          
          

           

          Now the view is correct with null values for pages that have no data for P13.

          2013-08-29 13-48-58.png

           

          Calculating Previous Period

          You can follow a similar procedure to find the page view for the previous period:

          Previous Period =

          'P' + STR(WINDOW_MAX(MAX([Period Number]))-1)
          
          

           

          Page Views Previous Period =

          IF MIN([Period]) == [Previous Period]
          THEN SUM([Page Views])
          END
          
          

           

           

          Calculating Period over Period (POP) Change

          Your first thought might be to use a simple calculated field: ([Page Views Current Period] - [Page Views Previous Period]) / [Page Views Previous Period]. But there is a conflict. When the condition for current period is TRUE, the previous period is FALSE and the result will be nulls.

           

          The solution is to use another table calc to find the current and previous periods over all periods. I used this formula

          POP Change =

          (WINDOW_MAX([Page Views Current Period]) - WINDOW_MAX([Page Views Previous Period])) /
          WINDOW_MAX([Page Views Previous Period])
          
          

           

          When compute using is set to Period, all of the Page Views ... fields have access to all periods. When you add this, you'll notice  a common problem with table calcs, there are multiple marks for each value, because the formula is calculated for each period.

          2013-08-29 14-14-25.png

          Fix this by wrapping the formula in an IF FIRST() == 0.

          POP Change =

          IF FIRST() == 0 THEN
          (WINDOW_MAX([Page Views Current Period]) - WINDOW_MAX([Page Views Previous Period])) /
          WINDOW_MAX([Page Views Previous Period])
          END
          
          




          Creating the View With Multi-axis Crosstab

          To create the final view, I duplicated the last Step3 worksheet above. To remove the extra white space, from the top menu bar, click on Analysis > Stack Marks > Off.

           

          You can double-click on the Page Views Current Period and POP Change to add these columns. Click on the current period and click the sort icon.

          2013-08-29 14-21-25.png

           

          Now it's starting to look like your goal, except that you want a bar graph in each cell. This is where the Drummey's multi-axis crosstab trick is used.

           

          Start by duplicating the view and then:

          • CTRL-click the green pills in the Measure Values shelf and drag them to the Columns shelf.
          • On the Marks shelf, All should be visible. Click Color and set transparency to 30%, and Border to None.
          • Also on All, Click Label and select left-align.
          • Next click on the POP Change marks, select Shape. Click Color > Transparency 0% to hide these marks. Click Label and unselect Show Mark Labels. Since you want shapes here, we'll do this with a dual axis below.
          • Right click on the Null indicate and select Hide Indicator.

           

          Getting closer:

          2013-08-29 14-30-38.png

           

          Still a couple of problems. First, the axis labels are at the bottom and we want them at the top. Second, we want to show the arrow indicator and value for POP change. We'll solve both by creating another dummy axis with a calculated field called Z- Lift Title to Top =

          0.0
          
          

           

           

          Drag this field next to the Page Views Previous Period pill. Then

          • Click on the Page Views Previous Period pill and select Dual Axis.
          • Select the All Marks card. Remove Measure Names from the color shelf.
          • Select the Z-Lift Title to Top Marks card. Click Color  and set transparency to 0%. Click Label and unselect Show Mark Labels.
          • Select the Page Views Current Period Marks card and select Bar graph.

           

          Repeat the above for Page Views Current Period and POP Change, dragging a second copy of Z-Lift Title to Top beside these pills.

           

          To get the shape and label for the POP change. Create one more dummy calculated field called

          Z-0.0 =

          0.0
          
          

           

          Replace the POP Change pill with Z-0.0. Then select Z-0.0 in the Marks card and

          • Drag POP Change to the Label shelf.
          • Select Shape mark type.
          • Click Label and select Right alignment.

           

          Right-click on the Z-0.0 axis and set this to -0.15 to 1 to move the shapes over to the left side of the pane.

           

          To add the arrow shapes, create a new calculated field POP Change Shape

          [POP Change] > 0
          
          

           

          I also use this field for color, but you could create a more sophisticated color scheme that depends on the magnitude of the POP change.

           

          Drag this pill to the Shape self and to the Color shelf for Z-0.0 Mark card.

          • In the shape legend, select the arrow in the upper right > Edit Shapes and select the up and own arrows. Leave the null values as a circle.
          • In the color legend, select Edit Colors and select the colors you want for the arrows. Double click on Null value and select white.

           

          Finally a few cleanups.

          • From the top menu bar, select Format > Lines > Sheet and set Zero Line to None.
          • Right click on each of the bottom axes >Edit Axis. Delete the Title. Select Tick Marks > None.
          • Right click on each of the top axes and set Tick Marks to None. Change the title of Z-0.0 to "POP Change".
          • Right-click on the Page label in the upper left corner and select Hide Field Labels for Rows.

          2013-08-29 15-27-04.png

           

          This is looking good, but there are a couple of final points: sort order and rank.

           

          Remember that we manually sorted the pages by Page Views Current Period above. To do this automatically requires another trick. Normally you could select Page > Sort Order ..., but you cannot sort by a table calculation, such as Page Views Current Period. The workaround is to create another column in the left-most position, which Tableau will then use to sort. Since we want it to the left of Page and Page is a blue, discrete pill, the new field also needs to be discrete.

           

          I created Page Views Sort Order =

          -ZN(WINDOW_MAX([Page Views Current Period]))
          
          

           

          WINDOW_MAX(), with compute using set to Period, is required to cover all periods and prevent NULLs. ZN returns 0 for null values where there is no page view data. And, finally, since this column is sorted smallest to largest, making it negative puts the largest values on top.

           

          Now that the sort order is correct, click on the Page Views Sort Order and unselect Show Header to hide this column.

           

          I'm running out of time at the moment and adding rank is more complicated than using the INDEX() formula, so I'll hold off on this. Assuming I'm interpreting your goal correctly (and it hasn't changed), I can take a look at adding rank---or perhaps percentile would be more useful.

           

          Also take a look at the parameter-based approach in the workbook (if you followed the above, it'll be obvious how this works). This eliminates the need for table calcs and should provide somewhat better performance. Rank is also easy to add in this case. The disadvantage is that the user has to enter the period they're interested in seeing.

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Please help creating period over period and year over year comparison using attached excel sheet!
            Dheeraj Poojari

            Jim,

             

            I am still amazed at how finely you described this query, and solved it so efficiently. I couldn't wait to appreciate your help! You made my life so easy, that I owe you a beer..****, I owe you a barrel of beer!

             

            Many thanks, and I know who to reach out to going forward, when there are any queries!

             

            Regards,

            Dheeraj

            • 3. Re: Please help creating period over period and year over year comparison using attached excel sheet!
              Jim Wahl

              You're welcome! Let me know if you have any issue trying to replicate the above.

               

              Jim