Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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,