Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
schumi1980
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
timpoismans
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
juraj_misina
Partner
Partner

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
Specialist
Specialist

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

sasiparupudi1
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 ?

schumi1980
Contributor III
Contributor III
Author

Hi Sasidhar

Yes, for just InceptionDate and ExpiryDate it works properly.

It is a date.

sasiparupudi1
Master III
Master III

May be try

sum({

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

*

<MaximumInceptionDate={'>15.07.2018'}>

} Premium)

schumi1980
Contributor III
Contributor III
Author

Unfortunately the same results, the MaximumInceptionDate breaks the InceptionDate.

timpoismans
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.

juraj_misina
Partner
Partner

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.