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

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

    renske.hercules.0

      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!!

       

       

      ShowAired OnViewers (000)Should show
      Show+A2:D32 123-2-20151.124,00Not available
      Show 12-3-20151.122,600%
      Show 19-3-20151.127,500%
      Show 116-3-20151.169,604%
      Show 123-3-20151.294,8011%
      Show 130-3-2015987,90-24%
      Show 113-4-2015917,10-7%
      Show 120-4-20151.077,7018%
      Show 127-4-2015817,20-24%
      Show 14-5-2015695,40-15%
      Show 111-5-2015810,1016%
      Show 118-5-2015933,4015%
      Show 11-6-2015763,60-18%
      Show 114-9-20151.008,3032%
      Show 121-9-2015969,50-4%
      Show 128-9-2015840,80-13%
      Show 15-10-20151.009,5020%
      Show 112-10-2015921,10-9%
      Show 119-10-2015935,802%
      Show 126-10-2015859,40-8%
      Show 12-11-20151.091,1027%
      Show 116-11-2015980,30-10%
      Show 123-11-2015950,70-3%
      Show 130-11-2015985,704%
      Show 17-12-20151.237,9026%
      Show 24-7-2015986,90Not available
      Show 218-7-2015842,80-15%
      Show 28-8-20151.042,2024%
      Show 215-8-2015840,60-19%
      Show 25-9-2015733,60-13%
      Show 231-10-2015661,70-10%
      Show 212-12-2015813,2023%
      Show 313-1-2015963,60Not available
      Show 318-1-2015537,90-44%
      Show 320-1-2015894,9066%
      Show 327-1-20151.029,9015%
      Show 33-2-20151.206,1017%
      Show 328-3-2015150,80-87%
      Show 312-6-2015194,2029%
      Show 34-8-2015293,7051%
      Show 311-10-2015181,80-38%
        • 1. Re: Calculate perfomance (%) Compared to performance of 'latest date'
          Andrew Watson

          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'
            renske.hercules.0

            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'
              Andrew Watson

              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'
                renske.hercules.0

                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'
                  Andrew Watson

                  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):

                   

                  Select Compute using Advanced to see the below. Drag your new fields into Addressing if required:

                   

                   

                  It's likely a lot of trial and error will be required here as this is difficult to understand.

                   

                  Andrew