CODEWITHSUNDEEP
tableau-api
santhosha
santhosha

Reputation: 440

Difference between two values - same Dimension, same Measure

As you can see from the picture above I am trying to add new column and to calculate the difference between =2014-2017.

Is there any way to make this because Tableau's option "Table Calculation" doesn't play role for me.

enter image description here

Upvotes: 2

Views: 4812

Answers (1)

SuperSecretAndNotSafeFromWork
SuperSecretAndNotSafeFromWork

Reputation: 1311

Working out the difference between the first and last periods with table calculations:

First you need to get minimum year's values (i'm calling the field "Min Year Select"):

IF DATETRUNC('year',[Order Date]) = 
 {FIXED: MIN(DATETRUNC('year',[Order Date]))}
   THEN 1 END

The above field named Min Year Select is saying that it should return a 1 if the year of the order date is the minimum year in your date range

Now we are flagging the smallest years, we can create a field to get the values (i'll call this "Min Year Segment"):

IF SUM([Min Year Select]) >= 1 THEN [Sales] END

Here we're saying that if the year is flagged as the smallest (as classified by the previous calc field we made), then get the value

But before we can compare the two values, you have to work out the number of time periods between the min and current year so that the difference calculation lookup field is comparing the right values (i'll call this "Number Years in Range"):

{FIXED [Segment]: COUNTD(DATETRUNC('year',[Order Date]))}

What we're doing is fixing the query at the category level (segment), think of this as removing the date pill from your report, then performing a calculation. Here it's COUNT DISTINCT years. So if a segment has data for 2011,2012,2013; then the query returns 3

We can now get the difference between your latest and your minimum Segments (called: "Difference from First Last Segment"):

[Segment] - 
LOOKUP([Min Year Segment],
   -1*(Number Years in Range)-1)

Firstly we get the first year's sales for each segment (Min Year Segment will be null for all years that aren't the first, so we need to lookup the first by going backwards by the number of years in our range:

We do -1 * because we want the lookup to lookup backwards, then we add in ("Number Years in Range" - 1) because we want to lookup to the period that had the earliest data. We do minus one so we're excluding the current year/latest year in your dataset

This is a lot to digest, I think it's easier to present as a picture too:

Here we calculate the difference between the first and last month, with the value in the last month

enter image description here

If this helped or you have any more questions, please vote on my answer/let me know

Upvotes: 1

Related Questions