Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set analysis

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

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

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.

View solution in original post

8 Replies
Highlighted
Luminary
Luminary

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

Highlighted
Specialist
Specialist

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

Highlighted
Master III
Master III

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 ?

Highlighted
Contributor III
Contributor III

Hi Sasidhar

Yes, for just InceptionDate and ExpiryDate it works properly.

It is a date.

Highlighted
Master III
Master III

May be try

sum({

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

*

<MaximumInceptionDate={'>15.07.2018'}>

} Premium)

Highlighted
Contributor III
Contributor III

Unfortunately the same results, the MaximumInceptionDate breaks the InceptionDate.

Highlighted
Specialist
Specialist

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.

View solution in original post

Highlighted
Luminary
Luminary

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.