5 Replies Latest reply on Jan 7, 2016 6:55 AM by Andrew Watson

Calculate perfomance (%) Compared to performance of 'latest date'

Hi everyone,

I try to make a calculated field in Tableau to calculate the episode-episode growth for TV ratings. In my data I dont have episode number, but just dates. This means that it should calculate the difference in viewers for 2 episode, looking at to following dates.

To clarify my problem I added dummy data (also in an excel sheets for anyone whos interested). It should work like this: ( "Viewers of the episode on -for example- July 1" - "Viewers of the episode on the airing date before July 1, this could be June 30, but also Jun 26")/ "Viewers of the episode on the airing date before July 1, this could be June 30, but also Jun 26"

Could anyone help me with this?

Thanks a lot!!

 Show Aired On Viewers (000) Should show Show+A2:D32 1 23-2-2015 1.124,00 Not available Show 1 2-3-2015 1.122,60 0% Show 1 9-3-2015 1.127,50 0% Show 1 16-3-2015 1.169,60 4% Show 1 23-3-2015 1.294,80 11% Show 1 30-3-2015 987,90 -24% Show 1 13-4-2015 917,10 -7% Show 1 20-4-2015 1.077,70 18% Show 1 27-4-2015 817,20 -24% Show 1 4-5-2015 695,40 -15% Show 1 11-5-2015 810,10 16% Show 1 18-5-2015 933,40 15% Show 1 1-6-2015 763,60 -18% Show 1 14-9-2015 1.008,30 32% Show 1 21-9-2015 969,50 -4% Show 1 28-9-2015 840,80 -13% Show 1 5-10-2015 1.009,50 20% Show 1 12-10-2015 921,10 -9% Show 1 19-10-2015 935,80 2% Show 1 26-10-2015 859,40 -8% Show 1 2-11-2015 1.091,10 27% Show 1 16-11-2015 980,30 -10% Show 1 23-11-2015 950,70 -3% Show 1 30-11-2015 985,70 4% Show 1 7-12-2015 1.237,90 26% Show 2 4-7-2015 986,90 Not available Show 2 18-7-2015 842,80 -15% Show 2 8-8-2015 1.042,20 24% Show 2 15-8-2015 840,60 -19% Show 2 5-9-2015 733,60 -13% Show 2 31-10-2015 661,70 -10% Show 2 12-12-2015 813,20 23% Show 3 13-1-2015 963,60 Not available Show 3 18-1-2015 537,90 -44% Show 3 20-1-2015 894,90 66% Show 3 27-1-2015 1.029,90 15% Show 3 3-2-2015 1.206,10 17% Show 3 28-3-2015 150,80 -87% Show 3 12-6-2015 194,20 29% Show 3 4-8-2015 293,70 51% Show 3 11-10-2015 181,80 -38%
• 1. Re: Calculate perfomance (%) Compared to performance of 'latest date'

Does the attached work? I created a calculated field to show the previous value and another to calculate the % difference. I though this would be possible using the in-built Percent Difference but it wouldn't restart for each Show by default hence I created it in 2 calc fields instead.

Andrew

• 2. Re: Calculate perfomance (%) Compared to performance of 'latest date'

Hi Andrew,

Thank you for your superquick reply! It does seem to work, but it’s not exactly what I am looking for.

I attached a new file with a fake ‘report’of how I will use it/how I want it to show up in a Tabeal Sheet. Some comments:

• In a report I want to show a list of shows that aired in the last -for example- 7 days. This will be a list of about 50 different shows with their latest episodes shown. For all these latest episodes, I want to show the peformance compared to the episode before BUT those episodes wont show up in the final report..
• All columns will be sorted on the "On Air"column, from newest to oldest
• This blue column (Ep) is just to clarify my story, but is  NOT available in my data and will NOT be shown in the final report.

1. I hope you (or someone else) will find a solution J!

Thanks

• 3. Re: Calculate perfomance (%) Compared to performance of 'latest date'

That's far more complicated...what you actually want to do is calculate the % difference but then filter out the show which you're using for the calculation.

This wouldn't work with standard calculations as filtering out the values needed to calculate the difference would mean the calculation fails as the data is no longer available to calculate.

A workaround is to use a Table Calculation as a filter. These filters happen after the view has been calculated - meaning your % difference remains calculated but not displayed. I've converted the date field to a table calculation and applied that to the attached.

Note the dates or episode numbers supplied look a bit dodgy - the latest episode in number is the earliest date aired.

Andrew

• 4. Re: Calculate perfomance (%) Compared to performance of 'latest date'

Hi Andrew,

Thanks a lot for the explanation and the file you posted! I never heard of table calculation before. It works perfect now.. But when I add other dimensions to my sheet, it starts interfering with the Table Calculations.

For example:

If I add Airing Time of a show, and Channel name where the show is airing, the "PreviousValue" starts to disappear.

When I look at the Table Calculation of "PreviousValue" the discription says "Results are computed along Date (sorted descending by Count of Date) for each Channel, Show, Starttime, GenreType."

Could you explain why this is happening?

Thanks!

• 5. Re: Calculate perfomance (%) Compared to performance of 'latest date'

Yes, to get the calculation to reset for every show I had to go into the settings of the table calculation and tell it what to use for the calculation. That's not a topic to get into here, there are many different bloggers that have written about that, I think Jonathan Drummey has written extensively about this and I'm sure will happily point you in the right direction on his blog.

As a pointer you need to Edit the table calculation (click on the arrow on the pill):