Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
maxgro
MVP
MVP

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
Author

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
Specialist II
Specialist II

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
Author

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
Creator
Creator

Hi,

try this

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

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



Regards,

Dharma.

Not applicable
Author

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
Author

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
Creator
Creator

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
Author

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

Joe