Set analysis

[schumi1980]

Hi All

I have a problem with my set analysis.

I have the following expression:

=sum({<InceptionDate={'<02.09.2018'},ExpiryDate={'>01.09.2018'},Status={'Final'},MaximumInceptionDate={'>15.07.2018'}>} Premium)

I think that this expression would yield all premium from policies which

Have an inceptiondate <02.09.2018, an expiry date >01.09.2016 and a maximuminceptiondate >15.07.2018.

However, when I look at the results the maximuminceptiondate breaks the inceptiondate condition, I have a policy with inceptiondate 01.10.2018 in the results.

Where do I make something wrong? Is the ordering of the conditions important?

Many thanks.

Best regards,

Jan

[juraj_misina]

Hello Jan,

you should use double quotes for searches in set analysis and single quotes for string literals, so your expression should look like this:

=sum({<InceptionDate={"<02.09.2018"},ExpiryDate={">01.09.2018"},Status={'Final'},MaximumInceptionDate={">15.07.2018"}>} Premium)

Hope this helps.

Juraj

[timpoismans]

Also make sure your date format is the same as the values you are comparing too in the set analysis.

[sasiparupudi1]

Do you see the correct results when you just did the following ?

sum({<InceptionDate={'<02.09.2018'},ExpiryDate={'>01.09.2018'},Status={'Final'}>} Premium)

What type of data in InceptionDate/ExpiryDate ? string/Date ?

[schumi1980]

Hi Sasidhar

Yes, for just InceptionDate and ExpiryDate it works properly.

It is a date.

[sasiparupudi1]

May be try

sum({

<InceptionDate={'<02.09.2018'},ExpiryDate={'>01.09.2018'},Status={'Final'}>

*

<MaximumInceptionDate={'>15.07.2018'}>

} Premium)

[schumi1980]

Unfortunately the same results, the MaximumInceptionDate breaks the InceptionDate.

[timpoismans · Accepted solution]

Try making a table with the following dimensions:

• InceptionDate
• ExpiryDate
• Status
• MaximumInceptionDate

And add Sum(Premium) as a measure.

Then try making the selection as you are doing in your set analysis. InceptionDate < 02.09.2018 and so on.

If the set analysis works without the MaxInceptionDate, perhaps something in your data is behaving as you expect it to.

[juraj_misina]

In such case I'd come back to check your date format. From your posts I figure you want to use DD.MM.YYYY date format. Are all those fields formated to that format? Because if inceptiondate would be in MM.DD.YYYY format, then 01.10.2018<02.09.2018.

+ do what Tim suggested earlier today, create a table with those dimensions and measure and apply filters, see what happens.