8 Replies Latest reply on Feb 22, 2016 9:00 PM by angelina.fleckenstein

# Calculate the difference of a ratio number between months that are in consecutive years

Hello,

I'm very new to Tableau and would really appreciate some help with what I'm trying to do. I need to calculate the difference of a ratio number between months that are in consecutive years. Then the difference should be represented in a shape (up,down arrow, side arrow).

My data set has columns: Year, building id, buildingname, month number, month description, totaltargetempl, totalempl

For each month, building and year, the number represented is sum(totaltargetempl) / sum(totalempl)

The viz needs to display only 2 most recent years and 2 or 3 most recent months.

The difference should be calculated for month across the 2 years - the number should be displayed and a visual should represent if the number is up or down compared to previous year's month.

Example: August 2015 has 0.97 = 97%, August 2014 has 0.96 = 96%. the difference is 1% so the change should be displayed as up arrow.

If current month is February, data needs to be displayed for February 2016, February 2015, January 2016, January 2015

Here is how i'm trying to represent it.

I tried creating multiple calculated fields and table calculations, using last(), lookup(), and other functions, but cannot seem to get it right.

How do I return the correct values for the current year/month and previous year/month? Tried multiple partition/attribute combinations, but can't get it right.

How do I add a separate column just for the shape? when I create a KPI and add to color and shape, it gets added in the same cell as the % number displayed.

I have attached  .twbx as an example data set with the fields I have. I removed all the gazzilion calculated fields I created and played with so there is no clutter.

Thank you very much

• ###### 1. Re: Calculate the difference of a ratio number between months that are in consecutive years

Done , but quite tricky.

I used table calc, with hiding >12 months old data <<Year Diff>>...after filtering 0~2 months old  data <<Date Header>>.

[Date_Header]  // Used as filter to Show only this month, -1 month, and -2 month of this year, last year

if datediff('month',[Date],today())=0 or

datediff('month',[Date],today())=1 or

datediff('month',[Date],today())=2 or

datediff('month',[Date],today())=12 or

datediff('month',[Date],today())=13 or

datediff('month',[Date],today())=14

then [Date]

end

[Year_Diff]

int(datediff('month',[Date],today())/12)

// for the purpose of grouping <0~-11 month>,  <-12 month+ older  > Keeping Table calc, but want to hide base month of last year

// You can understand why I add this field in the sheet "Why Year Diff Needed?"

Then, put Three MIN([Number of Records]) field into Column to have three different measure independently

1. [EmplRatio]

SUM([Total Target Emp])/Sum([Total Emp])

2. [EmplRatio] Table calc difference from previous Year Diff which is sorted as descendant

3. KPI up/down with shape and color,

[KPI]

if ZN([EmplRatio]) - LOOKUP(ZN([EmplRatio]), -1)<=0.05 then "Up" else "Down" END

Hide "1" of Year Diff

Why [Year Diff] Needed?

There is another way to use LOD, but that way is also troublesome, maybe this way is a little bit easier....

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Calculate the difference of a ratio number between months that are in consecutive years

Hi Shin,

thank you so much for the explanation and your help. I really appreciate it. I am going over everything, seeing what you did. I think I may have couple further questions, but want to make sure I understand it all before asking. Thank you so much!

• ###### 3. Re: Calculate the difference of a ratio number between months that are in consecutive years

It'll take time, I think.  As I said it's tricky, but for sure, good learning opportunity.

Don't hesitate ask questions at any time

Shin

• ###### 4. Re: Calculate the difference of a ratio number between months that are in consecutive years

Thank you so much. Yes - this is really good learning opportunity. Your approach to handle the previous/current year change is clever and it works! Thanks so much. I think I have it working. I added a column called "last year" ratio number to show the % of the ratio that was last year for the indicated month.

I added the table calc field to the column it at the end after I added "Current year", "change", "+/-". Do you know how to move it after "Current year"? When i try to just move or drag, it messes up the numbers for all other columns and it doesn't just move.

Also, what is the purpose for the "Measure Names" in the Filter section? How did you get the "Difference in EmplRatio from the Previous along FiscalYear" in the selection ? (When I right click -> Filter, i don't have that in there)

Is there an easy way to sort the months in order of most recent to furthest, 0 , -1, -2. Example: February (now and most recent), January (last month), December (furthest from now).

Thank you.

• ###### 5. Re: Calculate the difference of a ratio number between months that are in consecutive years

It was working file and I think you can safely remove Measure Name filter.

Sorting is another tricky one because "Month Description" is used to judge "Same Month" across year.

I mean adding year brings you incorrect data or null immediately.

Then the approach is same as year difference, calculate "Month Difference".

Then add these number on left side of "Month Description"

These 0,1,2  are unnecessarily information for users but need for correct sorting.

Put below field instead of "Month description"

[Month Description 2]

str(datediff('month',[Date],today())%12)+" "+[Month Description]

==fyi==  ///  Operators

## % (modulo)

This calculates a numeric remainder. For example, `5% 4 = 1`.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Calculate the difference of a ratio number between months that are in consecutive years

Yes, that worked! Again, thank you so much for your help on this - it has been good learning experience.

Best Regards,

Angelina

• ###### 7. Re: Calculate the difference of a ratio number between months that are in consecutive years

Happy to hear so.

One more request, if you can mark "helpful" again, it encourages me

Thanks,

Shin

1 of 1 people found this helpful
• ###### 8. Re: Calculate the difference of a ratio number between months that are in consecutive years

Done! Thank you.