topic Re: Set Analysis - correct total in QlikView

Set Analysis - correct total

Thu, 10 Oct 2013 15:56:41 GMT

I have the following expression in a pivot chart to calculate Current Year Premium based on the Previous Year Premium being nil. This works on a line by line basis, but the totals still include those figure where the prior year premiums are nil. Any idea how to overcome this?

([Prior Yr Premium]=0, 0,
sum(aggr((if(sum( {$<coveryear = {$(#vLastmonthyearB)}, ptrantype = {'R', 'N'},
inscode = {'RS17','TW01'}, %BusinessClassLink = {'20.22.','23.22.','52.22.'}>} netprem) <= 0, '0',
sum( {$<coveryear = {$(#vLastmonthyearB)}, ptrantype = {'R', 'N'},
inscode = {'RS17','TW01'}, %BusinessClassLink = {'20.22.','23.22.','52.22.'}>} netprem))),clntcode,covermonth)))

Thanks,

Jayne

Re: Set Analysis - correct total

Thu, 10 Oct 2013 16:19:16 GMT

Hi Jayne,

If I understand this correctly, your problem is with the test of "[Prior Yr Premium]=0"
failing. I would start by reducing your expression to something like

If ([Prior Yr Premium]=0, 0,999999)

This will prove that the test is working, and this would avoid the need to debug the other four lines, which look a little fearsome.

Good luck,

John

Re: Set Analysis - correct total

Fri, 11 Oct 2013 08:58:00 GMT

Hi,

Can you give us sample QVW or source?

I had the same need last year and I used Dimensionality() to manipulate my Total

Regards,

Alex

Re: Set Analysis - correct total

Fri, 11 Oct 2013 10:00:39 GMT

Thanks both.

Alex, I'm not really sure what you need from me?

Thanks,

Jayne

Re: Set Analysis - correct total

Fri, 11 Oct 2013 10:27:40 GMT

Well as I said a sample application (qvw) OR a sample source (excel or you can list an inline table here) OR the result table you want (where I can see the dimensions).

If you don't have any then that's okay. I can't explain it without showing a sample. Sorry