Set Analysis with Multiple Fields

Fri, 27 Aug 2010 10:27:23 GMT

I am trying to find an easier / more elegant way to do the following.

I have 2 basic data tables -> 1 is claim cost related and 1 is premium and policy related.

I used a set analysis in an expression as below to get a chart that shows claim cost over premium by year (which can be drilled down further to quarter).

sum (claims.cost) / sum ( {$ <claims.delay_band= , claims.lossyear= , claims.lossqtr= , claims.lossmth= , claims.lossday= , claims.reportyr=, claims.reportqtr= , claimcause.cat1= , claimcause.cat2= ,claimcause.cat3= ,claimtype.cat1= >} TOTAL <prem.Yr, prem.qtr> prem.prem)

The above expression is a shortened version of what I am using. I need to exclude most fields/dimensions that belong to all tables except the premium table (e.g. claims.x and claimcause.x) from interfering with the summing of the premium in the denominator.

The expression works currently. However, is there an easier way than manually typing all the fields into the {$ <...>} ? I was wondering if I can use something like {$ <claims.*= >}.

Thanks in advance for any suggestions.

AW:Set Analysis with Multiple Fields

brenner_martina — Fri, 27 Aug 2010 10:31:20 GMT

Hi Vanessaho,

what about using a variable, please try this:

SET vClaims = 'claims.delay_band= , claims.lossyear= , claims.lossqtr= , claims.lossmth= , claims.lossday= , claims.reportyr=, claims.reportqtr= , claimcause.cat1= , claimcause.cat2= ,claimcause.cat3= ,claimtype.cat1=';

sum (claims.cost) / sum ( {$ <$(vClaims) >} TOTAL <prem.Yr, prem.qtr> prem.prem)

AW:Set Analysis with Multiple Fields

Fri, 27 Aug 2010 12:16:54 GMT

Thanks Martina! It works perfectly!

topic Set Analysis with Multiple Fields in QlikView

Set Analysis with Multiple Fields

AW:Set Analysis with Multiple Fields

AW:Set Analysis with Multiple Fields