6 Replies Latest reply on Apr 16, 2014 3:45 AM by Prashant Sharma

    How do I write a formula to take the difference of data in the current period to data in a prior period if data only existed in one of the two period?  Part 2

    Sheridan Hodson

      I have added a dataset sample and a sample workbook for my question to help clarify the issue I am having with taking the difference of data points between two time periods.


      I am trying to compare data in one period to data in another period in order to see the differences between time periods.  The trouble I am having is with getting the formulas right in Tableau to take the difference of the data between the two time periods. My formulas are only taking the difference between data when the 'data event' exists in both periods.  The formulas are not taking into consideration the data points where the data description/event only existed in one of the two time periods.




      1)  I have a measure called "Net MI AT"     stands for Net Margin After Tax


      2)  I have created a calculated field to get the "Previous Net MI AT" with this formula:

                 ZN(LOOKUP(SUM([Net MI AT]),-1))


      3)  To get the difference in Net Margin After Tax "Differrence in Net MI AT" I created a 2nd calculated field with formula:

           ZN(Sum([Net MI AT])) - ZN([Prior Period Net Margin by Event])


      Here is a screen view of my sample data set which I simplified for this question with only 5 lines of data:

      Capture 4.JPG



      When I import the data into Tableau everything is recognized as expected for each Q1 2014 and Q2 2014:

      Capture 1.JPG


      There is no data point for Event C in Q1 since Event C only takes place in Q2 in this example. Note that all data is only for one specific refinery location.  When I set up a workbook to take the difference between Q2 - Q1 everything works correctly at a Refinery level (Q2 at 18 minus Q1 at 6 = 12)

      Capture 2.JPG


      The issue I am having is that when I try to look at the difference in Q2 - Q1 at a more detailed level (down to the event level where Event C only took place in Q2 and not Q1) then Tableau is no longer recognizing event C at all.  The calculations are only taking the difference for event A and event B into consideration. (Event A   3-2 =1 ,   Event B  5-4=1,  and Event C should diplay as 10-0=10 but instead is not appearing at all).  Am I making an error in how I am writing my formulas?  I am not sure what I am doing wrong, but I know I am making an error in my logic since Event C is not displaying at an Event level.


      Capture 3.JPG