topic Re: Help with Pivotal table please in QlikView

Help with Pivotal table please

Mon, 24 Sep 2012 23:53:46 GMT

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 — Tue, 25 Sep 2012 01:45:46 GMT

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 .

(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 — Tue, 25 Sep 2012 01:46:17 GMT

Hi Ksuraup,

(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

Tue, 25 Sep 2012 11:47:17 GMT

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,