Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Highlighted
Not applicable

Set Analysis question

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

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


9 Replies
Not applicable

Set Analysis question

In this case you should do something like

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

Sébastien

shumailh
Contributor III

Set Analysis question

Hi Graham,

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


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


Regards,
Shumail Hussain

Not applicable

Set Analysis question

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.

Highlighted
Not applicable

Set Analysis question

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

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


Not applicable

Set Analysis question

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
Contributor III

Set Analysis question

Try this,


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


Regards,
Shumail Hussain

Not applicable

Set Analysis question

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?

MVP
MVP

Set Analysis question


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

MVP
MVP

Set Analysis question


grahamwarwick wroteSmiley Frustratedhumail 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.