2 Replies Latest reply on Mar 11, 2015 6:58 AM by James Lapp

    How to calculate changes of a specific dimension attribute between two periods?

    James Lapp

      My data has two dimensions (week, and Cost Type - which has 4 attributes), one measure (amount), and four parameters. The purpose of the analysis is to compare the measure ($ amount) between periods, which are defined by the four parameters (period 1 start, P1 end, P2 start, P2 end).

       

      One of my Cost Types is CAPEX. I need to show two things in one table:

       

      (1) Show difference in CAPEX amount between periods

      (2) Show  difference in CAPEX as a % of total amount between periods (so, if CAPEX amount is $25 and total amount of all cost types is $100 in P1 and CAPEX amt is $30 and total amt of all cost types is $100 in P2, then I want calculation to show 5% [30%-25%]).

       

      One wrinkle in the analysis is that I need the period amounts to be the weekly average of the period so that if the periods selected by the user have a different number of weeks, it's comparing apples-to-apples.

       

      To do this, I created many calculated fields which, using IF statements, partitioned out "Amount" out into P1 and P2, and then isolated CAPEX (again using IF statements). I then created calculated fields subtracting P1 amounts from P2 amounts.

       

      Although the calculations at the total level (average total amount), when I get to the CAPEX level, I get 0's. All I need to do is show the correct calculations for the red cells below. I have attached the Tableau workbook (see Sheet 7). Any suggestions?

       

      Pic 2.PNG