Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis question

I am very novice to this and I think set analysis will do what i need.

I am using the following expression;

Count({<[Policy Status] = {'Cancelled'}>} [PolicyID])

Now this works fine for a simple count, however in the table i have 2 seperate date fields, one is a cancellation date and the other is a quoted date, if i just choose the cancellation month it gives the right data, if i also choose a quotation month then it alters the cancellation figure, i want to show the values independantly and not have one interfere with the other as it usually does with any linked tables.

I hope this makes sense

thanks in advance

Graham



1 Solution

Accepted Solutions
Not applicable
Author

In order to ignore the Quotation Month selection, try this:

Count({<[Policy Status] = {'Cancelled'}, [Quotation Month]=>} [PolicyID])


View solution in original post

9 Replies
Not applicable
Author

In this case you should do something like

Count({1 <[Policy Status] = {'Cancelled'}>} [PolicyID])

Sébastien

shumailh
Creator III
Creator III

Hi Graham,

Did u try using $ set analysis? may it works.


Count({$<[Policy Status] = {'Cancelled'}>} [PolicyID])


Regards,
Shumail Hussain

Not applicable
Author

I have tried both $ and 1, the problem comes that if i choose 1 then every cancellation ever shows, if i choose $ then just the selected year works, but when using $ if i choose another input field then it alters this field.

Not applicable
Author

In order to ignore the Quotation Month selection, try this:

Count({<[Policy Status] = {'Cancelled'}, [Quotation Month]=>} [PolicyID])


Not applicable
Author

Thats great, thanks so much for the help it works a treat.

Now i have this problem;

Count({<ActivityEffectedYear = {2009}>} [Beneficiary Status] = 'Cancelled',[BeneficiaryID])



Hopefully you can see what i am trying to do, i want to count the beneficiaryID when the beneficiary status is Cancelled and the activity effected year is > 2009. I think i have missed something obvious.

Thanks.





shumailh
Creator III
Creator III

Try this,


count({$<ActivityEffectedYear= {">2009"}, [Beneficiary Status] = {"Cancelled"}>} [BeneficiaryID])


Regards,
Shumail Hussain

Not applicable
Author

Shumail that worked thanks, although had to change the >2009 to 2009.

I have just realised that i also need to add in ActivityText = 'CANCEL INSTRUCTION'

Where would i add that in?

johnw
Champion III
Champion III


Shumail Hussain wrote:
Hi Graham,
Did u try using $ set analysis? may it works.
<blockquote>
Count({$<[Policy Status] = {'Cancelled'}>} [PolicyID]) </blockquote>

Regards,
Shumail Hussain<div></div>
Just a quick note - the $ is optional in this expression, and is the default behavior. So this expression will behave exactly the same and return exactly the same result with or without the $.

johnw
Champion III
Champion III


grahamwarwick wrote:Shumail that worked thanks, although had to change the >2009 to 2009.
I have just realised that i also need to add in ActivityText = 'CANCEL INSTRUCTION'
Where would i add that in?




count({<[ActivityEffectedYear] = {'$(=year(today()))'}
,[Beneficiary Status] = {'Cancelled'}
,[ActivityText] = {'Cancel Instruction'}
>} [BeneficiaryID])

You specified that you wanted >2009, so that's why Shumail did it that way. In any case, I wouldn't hard code values like 2009 into a real application unless you REALLY want 2009, no matter what year it currently is. I suspect that what you really want is the current year, whatever that year might be. The above should handle that.

I also I changed the double quotes to single quotes in the Beneficiary Status. You'll get the same result either way, but double quotes specify a search string. So you would have QlikView first search for all Beneficiary Statuses matching the search string "Cancelled". Only one can match, so it will find it and select it. Single quotes specify a literal, which SHOULD (untested, and based soley on basic design experience) short cut this process and therefore execute more quickly. In practice, I can't image how you would ever detect a difference. Still, I think it's helpful to understand the difference between literals and search strings, and to be explicit in the code about which is which.