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

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.

Example:

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:

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

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)

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.

• 1. Re: 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

HI,

Try to attach images in your query like you are attaching your .twbx file as two of the images are not showing properly in your query.

BTW, Have you tried to compute it on Event level? Is following are the correct values?

Warm Regards,

Prashant Sharma - India | LinkedIn

• 2. Re: 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

Hello Prashant,

Thank you again for your help.  I have reposted again with the attachments added.  I took a look at the values you sent over, but they do not appear to be correct.  Event A was \$2 in Q 1 and \$3 in Q2 so Q2-Q1 = \$1.  Event B was \$4 in Q1 and \$5 in Q2 so Q2-Q1 = \$1.  Event C did not exist in Q1 and was \$10 in Q2.  Q2 – Q1 = \$10.

The differences in Q2-Q1 for events A, B, and C are correct at a level where the descriptor exists in both periods (in my example at the Refinery level everything works correctly Q1 = \$6, Q2 = \$18, Q2-Q1 = \$12).  Once I try to view the data at a more granular level the calculation stops working (in my example when I look at the data at Event Level I see the difference in Event A =1, Event B =1 and C is not appearing at all).  The difference for Event A and Event B are correct, but the difference for Event C is no longer being included.

Thank you,

Sheridan

• 3. Re: 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

Hi,

You have to change compute using for "Selected Over Text" to Table(Down) will provide you correct values.

Warm Regards,

Prashant Sharma - India | LinkedIn

• 4. Re: 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,

Instead of applying  ZN directly on  [Net MI AT] , you can apply it on the lookup function of [Net MI AT].

ZN(Net MI AT)  returns  nothing because there is no rows for the Event C in the underlying data,  but

ZN( lookup(SUM([Net MI AT]),0) )  will return  0  because it is not computed directly on the underlying data but on the cell in the view.

So create a calculated field  Current Net MI AT

ZN( lookup(SUM([Net MI AT]),0) )    that you will also use in replacement of[ Net MI AT] in the  [Difference in Net MI AT]

Michel

• 5. Re: 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

Hello Prashant,

Thank you very much for your help.  Changing the calculation for “Selected Over Text” to table down did work as you explained.  I did have one additional question.  When I did this I noticed there were still two subsets of data.  The first subset is really the Q1 absolute data (this is Event A \$2 and Event B \$4 shown below in red) and the second subset of data which is the actual delta for Q2 minus Q1.  I only want the second subset of data to be displayed since it is the actual difference between the two periods.  I tried to hide the Q1 subset of data, but I was not able to find a way to hide that data and still have the calculation work correctly.  Is it possible to hide the Q1 subset in red while keeping the second subset (in blue) below?

If I added a field to show what each subset of data really is like adding the year/quarter into the rows/columns field the calculations no longer worked correctly.

Thank you,

Sheridan

• 6. Re: 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

Hi,

I love to help you here but I am now on vacation for 4 days due to election & Good Friday, so not able to help you out here. But pinging people who may help us here - Jonathan Drummey Matt Lutton Noah Salvaterra

Warm Regards,

Prashant Sharma - India | LinkedIn