5 Replies Latest reply on Nov 1, 2014 12:34 PM by Shawn Wallwork

    Converision issues

    Marc Rapparlie

      Hello,


      The issue:

       

      1) I am having raw data in hh:mm:ss (not daytime) for TV broadcasts

      2) I want to have an overview to create cumulative duration and shown in a graph in hh:mm:ss

       

      Does anybody has some experiences with this?

       

      Thanks and best regards

      Marc

        • 1. Re: Converision issues
          Shawn Wallwork

          Got a packaged workbook?

           

          --Shawn

          • 2. Re: Converision issues
            Marc Rapparlie

            here we are... would like to have all together in one graph!

            • 3. Re: Converision issues
              Shawn Wallwork

              So you can get these together in the same chart (see attached). But you're going to have troubles with labeling (you can't use Measure Values/Names in a calculated field. The main issue is the shape of your data. It will work better if you get Dseconds and Mseconds together in a single field. You can do this using a Custom SQL self UNION. I set this up on the last worksheet. Here's the code, the changed bits are in Bold:

               

              SELECT ['Raw HBL$'].[Audience Rate] AS [Audience Rate],

                ['Raw HBL$'].[Away Team] AS [Away Team],

                ['Raw HBL$'].[Datum] AS [Datum],

                ['Raw HBL$'].[Dauer] AS [Dauer],

                ['Raw HBL$'].[Dseconds] AS [Seconds],

                ['Raw HBL$'].[Event] AS [Event],

                ['Raw HBL$'].[Home Team] AS [Home Team],

                ['Raw HBL$'].[Land] AS [Land],

                ['Raw HBL$'].[Market Share] AS [Market Share],

                ['Raw HBL$'].[Match Action] AS [Match Action],

                ['Raw HBL$'].[Monat] AS [Monat],

                ['Raw HBL$'].[Programm] AS [Programm],

                ['Raw HBL$'].[Region] AS [Region],

                ['Raw HBL$'].[RW2] AS [RW2],

                ['Raw HBL$'].[Sendeanstalt] AS [Sendeanstalt],

                ['Raw HBL$'].[Sender] AS [Sender],

                ['Raw HBL$'].[Tag] AS [Tag],

                ['Raw HBL$'].[Type of Program] AS [Type of Program],

                ['Raw HBL$'].[Zeit] AS [Zeit],

                  'D' AS [Type of Seconds]

              FROM ['Raw HBL$']

               

              UNION ALL

               

              SELECT ['Raw HBL$'].[Audience Rate] AS [Audience Rate],

                ['Raw HBL$'].[Away Team] AS [Away Team],

                ['Raw HBL$'].[Datum] AS [Datum],

                ['Raw HBL$'].[Dauer] AS [Dauer],

              ['Raw HBL$'].[Mseconds] AS [Seconds],

                ['Raw HBL$'].[Event] AS [Event],

                ['Raw HBL$'].[Home Team] AS [Home Team],

                ['Raw HBL$'].[Land] AS [Land],

                ['Raw HBL$'].[Market Share] AS [Market Share],

                ['Raw HBL$'].[Match Action] AS [Match Action],

                ['Raw HBL$'].[Monat] AS [Monat],

                ['Raw HBL$'].[Programm] AS [Programm],

                ['Raw HBL$'].[Region] AS [Region],

                ['Raw HBL$'].[RW2] AS [RW2],

                ['Raw HBL$'].[Sendeanstalt] AS [Sendeanstalt],

                ['Raw HBL$'].[Sender] AS [Sender],

                ['Raw HBL$'].[Tag] AS [Tag],

                ['Raw HBL$'].[Type of Program] AS [Type of Program],

                ['Raw HBL$'].[Zeit] AS [Zeit],

                  'M' AS [Type of Seconds]

              FROM ['Raw HBL$']

              • 4. Re: Converision issues
                Marc Rapparlie

                this is great and amazing!!! Thanks so much!

                • 5. Re: Converision issues
                  Shawn Wallwork

                  Glad it worked for you!

                   

                  --Shawn