Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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:

SaleIDAPP TYPEHouseholdMonthlyIncomePLAN NAME
4a826894-6825-4150-9c62-928344725d23Basic1000WHOLE LIFE NON SMOKER
10a7e0a5-270a-48af-bc71-3945916dd366Basic1400WHOLE LIFE NON SMOKER
6381779d-c62c-4f2c-a705-574191a2aab3Senior PREFERRED LIFE SMOKER
e9d2eac8-1d29-41ab-991f-d78b8cb7e713Basic1000WHOLE LIFE NON SMOKER
f231b1d6-ddb2-4e3c-8559-a90a17f1ba54Basic7400PREFERRED LIFE SMOKER
10 Replies
MVP
MVP

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

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

Not applicable

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

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.

chematos
Valued Contributor II

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

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

Not applicable

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

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

dharmarasu
Contributor

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

Hi,

try this

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

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



Regards,

Dharma.

Not applicable

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

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.

Not applicable

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

QV1.jpgWhenever 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.QV2.jpg

dharmarasu
Contributor

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

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

Not applicable

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

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

Joe

Community Browser