1 Reply Latest reply: Sep 25, 2012 3:09 PM by ksurap2012 RSS

    How to present this data in Pivot table

      , can you please check below if you have a quick moment?

       

       

       

       

       

      (this is just an example to understand the data)

       

       

      Total Assets -  100

       

       

      Assets with CC = 60

       

       

      Assets without CC = 40

       

       

      Total Assets Delivered – 20 (20 out of 100)

       

       

      Assets delivered with CC = 5

       

       

      Assets delivered without CC -15

       

       

      The user wants data the following way. I tried to create expressions for the counts for Packages Scheduled and Packages Delivered in the Pivot table but the data does not get filtered when you click in the table. So I don't wan to go that route. So instead I am trying to do this in the script. But since Packages Delivered is a sub set of Packages Scheduled, correct data is not being returned. Please see the qvw attached. Any help you could provide wtih this is greaty appreciated. Basically I am trying to find a best way to get the values of Packages Scheduled and Packages Delivered with and without CC.

       

       

       

       

      assets.PNG

       

       

       

       

       

      The following are supposed to be the formulas for my counts.

       

       

      Count(if(IsClosedCaptioningAvailable='Y' ,AssetId)) as
      scheduledwcccount,

       

       

      Count(if(IsClosedCaptioningAvailable='N'  ,AssetId)) as
      scheduledwoutcccount,

       

       

      Count(AssetId) as scheduledcount,

       

       

      Count(if(IsClosedCaptioningConfirmed='Y' and IsClosedCaptioningValid='Y'
      and DisplayStatus ='Completed',AssetId)) as deliveredwcccount,

       

       

      Count(if((IsClosedCaptioningConfirmed='N' or
      IsClosedCaptioningConfirmed='U') and (IsClosedCaptioningValid='Y' or
      IsClosedCaptioningValid='N' or IsClosedCaptioningValid='U') and DisplayStatus
      ='Completed',AssetId)) as deliveredwoutcount,

       

       

      Count(if(DisplayStatus= 'Completed',AssetId)) as
      deliveredcount