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