Set Analysis with Multiple Fields

[#1]

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.

[brenner_martina · Accepted solution]

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)

[#3]

Thanks Martina! It works perfectly!