topic Set Analysis question in QlikView

Set Analysis question

Wed, 18 Nov 2009 20:38:32 GMT

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

Set Analysis question

Wed, 18 Nov 2009 20:48:03 GMT

In this case you should do something like

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

Sébastien

Set Analysis question

shumailh — Wed, 18 Nov 2009 20:52:54 GMT

Hi Graham,

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


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

Regards,
Shumail Hussain

Set Analysis question

Wed, 18 Nov 2009 21:17:33 GMT

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.

Set Analysis question

Wed, 18 Nov 2009 21:42:11 GMT

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

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

Set Analysis question

Wed, 18 Nov 2009 21:50:24 GMT

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.

Set Analysis question

shumailh — Wed, 18 Nov 2009 21:58:24 GMT

Try this,


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

Regards,
Shumail Hussain

Set Analysis question

Wed, 18 Nov 2009 22:39:42 GMT

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?

Set Analysis question

johnw — Thu, 19 Nov 2009 02:51:12 GMT


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

Set Analysis question

johnw — Thu, 19 Nov 2009 03:01:19 GMT


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.