3 Replies Latest reply: Sep 25, 2012 7:47 AM by ksurap2012 RSS

    Help with Pivotal table please

      How can I filter data in a different sheet based on the expression in a pivot table?

      I have a pivot table as follows

       

      Month Name               August           September     October

      Platform                     Cable  Dish     Cable Dish     Cable Dish

       

      Movies Scheduled

      With Captions               200     250     150    100     200       170        

      Without Captions          150     170     150      10        15        25

       

      Movies Delivered

      With Captions               100     250       50    100     200         70        

      Without Captions          90         70     150      10        15        25

                              

       

      I tried to create the above using a pivot table. I used expressions to show the count of movies with captions and without captions. The expressions are very different for Movies scheduled and Movies Delivered.

       

      For Movies Scheduled with Caption - I used the following expression

      Count( {$<[IsClosedCaptioningAvailable]={'Y'}>} AssetId )

       

      For Movies Delivered with Caption - I used the following expression

      Count( {$<[IsClosedCaptioningConfirmed]={'Y'},[IsClosedCaptioningValid]={'Y'},[DisplayStatus] ={'Completed'}>} AssetId )

       

      The data looks correct but when I click at the movies delivered with  caption or without caption count for any month, the data in other sheets in not being filtered out.

      I am very new to Qlikview. Can you please suggest me if there is a better way to do this?

        • Re: Help with Pivotal table please
          alvinford ford

          Hi Ksuraup,

           

          It's how qlikview behaves with the Pivot Table . You can create a field in the Script and display it as a dimension . Then you can select the Delivery Status from the Pivot table . Just make the Expression as count of Asset ID .

           

           

          if

          (IsClosedCaptioningAvailable='Y',"Packages Scheduled",
          if(IsClosedCaptioningConfirmed='Y' and IsClosedCaptioningValid='Y',[DisplayStatus] ='Completed', "Packages Delivered")) as Delivery

          Status,

           

          Hope this helps ...

           

          Regards,

          Alvin.

          • Re: Help with Pivotal table please
            alvinford ford

            Hi Ksuraup,

             

            It's how qlikview behaves with the Pivot Table . You can create a field in the Script and display it as a dimension . Then you can select the Delivery Status from the Pivot table . Just make the Expression as count of Asset ID .

             

             

            if

            (IsClosedCaptioningAvailable='Y',"Packages Scheduled",
            if(IsClosedCaptioningConfirmed='Y' and IsClosedCaptioningValid='Y',[DisplayStatus] ='Completed', "Packages Delivered")) as Delivery

            Status,

             

            Hope this helps ...

             

            Regards,

            Alvin.

              • Re: Help with Pivotal table please

                Alvin, thanks for your response.

                 

                When I added the following code in the script, I get a script error and am not able to determine what the error is. I made sure all the field  names are correct

                 

                 

                if (IsClosedCaptioningAvailable='Y',"Packages Scheduled",

                 
                if(IsClosedCaptioningConfirmed='Y' and IsClosedCaptioningValid='Y' and DisplayStatus ='Completed', "Packages Delivered")) as DeliveryStatus 

                 

                Can you figure out if I am doing any thing wrong here?


                Thanks,