topic Re: Set analysis in QlikView

Set analysis

schumi1980 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set analysis

juraj_misina — Fri, 28 Sep 2018 11:50:52 GMT

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

Re: Set analysis

timpoismans — Fri, 28 Sep 2018 11:55:44 GMT

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

Re: Set analysis

sasiparupudi1 — Fri, 28 Sep 2018 12:18:22 GMT

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 ?

Re: Set analysis

schumi1980 — Fri, 28 Sep 2018 13:56:38 GMT

Hi Sasidhar

Yes, for just InceptionDate and ExpiryDate it works properly.

It is a date.

Re: Set analysis

sasiparupudi1 — Fri, 28 Sep 2018 14:03:43 GMT

May be try

sum({

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

<MaximumInceptionDate={'>15.07.2018'}>

} Premium)

Re: Set analysis

schumi1980 — Fri, 28 Sep 2018 15:08:16 GMT

Unfortunately the same results, the MaximumInceptionDate breaks the InceptionDate.

Re: Set analysis

timpoismans — Mon, 01 Oct 2018 06:38:50 GMT

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.

Re: Set analysis

juraj_misina — Mon, 01 Oct 2018 12:24:40 GMT

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.