topic Re: Pivot Table exclude where sum = 0 in QlikView

Pivot Table exclude where sum = 0

Mon, 15 Dec 2014 16:14:40 GMT

Hi All,

I have created a pivot table that allows me to extract the attached data. I want to amend this so that the client only appears when the 'prior year premium' > 0.

*I tried adding if([Prior Year Premium] > 0 , 0 , before the 'Current Year Premium' sum, but this also omits lines that were 0 last year and > 0 this year.

I'm hoping someone can help me with this problem.

Please let me know if you need any more details.

Thank you

Re: Pivot Table exclude where sum = 0

erjohnso — Mon, 15 Dec 2014 16:28:31 GMT

How about some form of a calculated variable and set analysis to set your pivot table expressions as follows:

sum( {<[Prior Yr Premium]>0>} [Prior Yr Premium]}

sum( {<[Prior Yr Premium]>0>} [Current Yr Premium]}

Re: Pivot Table exclude where sum = 0

Mon, 15 Dec 2014 17:03:26 GMT

Thank you for your reply,

Here are the expressions I use for prior and current year premiums:

Prior Year Premium:

sum(aggr((sum( {$<coveryearW = {$(#vThismonthyearA)}, ptrantypeW = {'R', 'N'},
BusinessClassTrimmedW = {'16','12','13','14','15','17','20','21','23','24','25','28','29','30','31','52','53','54','55','56','59'}>} totnetW)),AE,clntcode,coname,busiclass,covermonth))

Current Year Premium:

sum(aggr((sum( {$<coveryearC = {$(#vThismonthyearB)}, ptrantypeC = {'R','N'},
BusinessClassTrimmedC = {'16','12','13','14','15','17','20','21','23','24','25','28','29','30','31','52','53','54','55','56','59'}>} totnetC)),AE,clntcode,coname,busiclass,covermonth))

Please can you explain how I would apply your logic to these expressions?

Thank you

Re: Pivot Table exclude where sum = 0

erjohnso — Mon, 15 Dec 2014 20:30:14 GMT

I apologize, I am thinking out loud here and not in front of my application to test...but I think we can get close.

In your script, define the aggregate sum needed.

See: Aggregate function at script level - using QVD

Then, you could set bookmarks to the selections in your set analysis:

PY = coveryearW = {$(#vThismonthyearA)}, ptrantypeW = {'R', 'N'},
BusinessClassTrimmedW = {'16','12','13','14','15','17','20','21','23','24','25','28','29','30','31','52','53','54','55','56','59'}

CY = coveryearC = {$(#vThismonthyearB)}, ptrantypeC = {'R','N'},
BusinessClassTrimmedC = {'16','12','13','14','15','17','20','21','23','24','25','28','29','30','31','52','53','54','55','56','59'}>}totnetC)),AE,clntcode,coname,busiclass,covermonth))

Then set analysis as the expression as I stated above... sorry not completely clear. Let me get back to my computer, but didn't want to lose the thought as I had it.