topic Re: Ignore set analysis when filtering on a field value HELP!!! in QlikView

Ignore set analysis when filtering on a field value HELP!!!

Wed, 23 Jul 2014 15:17:34 GMT

I need to sum premium amounts where the PLAN NAME includes the word Life or Health and when the MONTHLY INCOME is greater than $1,000. Then I also need to include PLANs that are identified as a APP TYPE of Senior regardless of monthly income and plan name. My problem is when I filter on other APP TYPES I cant get exclude the Senior APP TYPES. Any suggestions. would be greatly appreciated....

Sum({$<PlanName={'*Life*','*Term*''}>*<HouseholdMonthlyIncome={">1000"}>+<AppType={'Senior''}>}AnnlzdPremium)

Data Sample:

SaleID	APP TYPE	HouseholdMonthlyIncome	PLAN NAME
4a826894-6825-4150-9c62-928344725d23	Basic	1000	WHOLE LIFE NON SMOKER
10a7e0a5-270a-48af-bc71-3945916dd366	Basic	1400	WHOLE LIFE NON SMOKER
6381779d-c62c-4f2c-a705-574191a2aab3	Senior		PREFERRED LIFE SMOKER
e9d2eac8-1d29-41ab-991f-d78b8cb7e713	Basic	1000	WHOLE LIFE NON SMOKER
f231b1d6-ddb2-4e3c-8559-a90a17f1ba54	Basic	7400	PREFERRED LIFE SMOKER

Re: Ignore set analysis when filtering on a field value HELP!!!

maxgro — Wed, 23 Jul 2014 17:19:47 GMT

maybe

Sum({$<[PLAN NAME]={'*Life*','*Term*'},HouseholdMonthlyIncome={">1000"}>+<[APP TYPE]={'Senior'}>}AnnlzdPremium)

field name are case sensitive, your data sample has PLAN NAME, your expression PlanName

Re: Ignore set analysis when filtering on a field value HELP!!!

Wed, 23 Jul 2014 17:59:48 GMT

Thank you for the reply even with the corrected case sensitive field names it still doesn't ignore the 'Senior' APP TYPE whenever I filter on a different APP TYPE.

Re: Ignore set analysis when filtering on a field value HELP!!!

chematos — Wed, 23 Jul 2014 18:15:31 GMT

Hi Robyn,

You could use the conditional in order to use an expression or another depending on the selection of AppType field.

You could use 2 expressions:

1. Sum({$<PlanName={'*Life*','*Health*''}, HouseholdMonthlyIncome={">1000"}>+<[APP TYPE]={'Senior''}>}AnnlzdPremium)

Use conditional box for this expression: GetSelectedCount([APP TYPE]) = 0

So, if there is no selection for [APP TYPE], this expression will be calculated

2. Sum({$<PlanName={'*Life*','*Health*''}, HouseholdMonthlyIncome={">1000"}>}AnnlzdPremium)

Use conditional box for this expression: GetSelectedCount([APP TYPE]) > 0

So, if there is a selection for [APP TYPE], this expression will be calculated

Hope this helps, regards

Jose

Re: Ignore set analysis when filtering on a field value HELP!!!

Thu, 24 Jul 2014 11:18:07 GMT

Just in case...

I believe there is a syntax error on the highlighted areas:

Sum({$<PlanName={'*Life*','*Term*''}>*<HouseholdMonthlyIncome={''>1000''}>+<AppType={'Senior''}>}AnnlzdPremium)

I believe, those should be a single quotes

Re: Ignore set analysis when filtering on a field value HELP!!!

dharmarasu — Thu, 24 Jul 2014 11:59:20 GMT

Hi,

try this

Sum({$<[PLAN NAME]={'*Life*','*Term*'},[APP TYPE]=,HouseholdMonthlyIncome={">1000"}>}AnnlzdPremium)+

Sum({1<[APP TYPE]={'Senior'}>}AnnlzdPremium)

Regards,

Dharma.

Re: Ignore set analysis when filtering on a field value HELP!!!

Thu, 24 Jul 2014 14:46:05 GMT

If I choose to filter on the APP TYPE value of Senior or Basic (the other value type) in my multi box then the results still don't show one or the other. The results ignore the filter.

Re: Ignore set analysis when filtering on a field value HELP!!!

Thu, 24 Jul 2014 15:49:33 GMT

Whenever I a select a value for APP TYPE the expression behind the ALP doesn't allow the fileter to be reflected in the premium total.

Re: Ignore set analysis when filtering on a field value HELP!!!

dharmarasu — Fri, 25 Jul 2014 04:45:27 GMT

so u need irrespective selection of all APP TYPE AnnlzdPremium total right?

try this

Sum({$<[PLAN NAME]={'*Life*','*Term*'},[APP TYPE]=,HouseholdMonthlyIncome={">1000"}>}AnnlzdPremium)+

Sum({1<[APP TYPE]=>}AnnlzdPremium)

Regards

Dharma

Re: Ignore set analysis when filtering on a field value HELP!!!

Fri, 25 Jul 2014 06:25:39 GMT

Doing two sums like that will give you an over inflated number, don't think that is what he is after

Joe

Re: Ignore set analysis when filtering on a field value HELP!!!

Fri, 25 Jul 2014 06:28:13 GMT

also

Sum({1<[APP TYPE]=>}AnnlzdPremium)

is giving you the whole data set, by the 1 being there, the [APP TYPE] modifier isn't actually doing anything

Joe