topic Re: Multiple Criteria with GetFieldSelections() in QlikView

Multiple Criteria with GetFieldSelections()

lucasdavis500 — Thu, 19 Jan 2017 18:41:38 GMT

is there a way I can do a formula inside my IF() statement where if multiple field selections are made a value is displayed?

Here's my formula:

=IF(GetSelectedCount(Product) = 0, Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} CanFurnishings),

IF(GetFieldSelections(Product) = 'Vehicle/Home EQ', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon)),

IF(GetFieldSelections(Product) = 'Financial Card', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} CanFurn_FinC),

IF(GetFieldSelections(Product) = 'Vehicle/Home EQ' AND GetFieldSelections(Product) = 'Financial Card', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon + CanFurn_FinC))

))))

Everything works fine except:

IF(GetFieldSelections(Product) = 'Vehicle/Home EQ' AND GetFieldSelections(Product) = 'Financial Card',

I want the values of :

(CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon + CanFurn_FinC)

to be displayed when user selects Veh/Home + Financial Card. Any advice would be appreciated

Re: Multiple Criteria with GetFieldSelections()

Anonymous — Thu, 19 Jan 2017 18:55:18 GMT

It looks like your if statement would never get to that last option because it would stop at this part.

IF(GetFieldSelections(Product) = 'Vehicle/Home EQ', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon)),

My recommendation would be to use multiple expressions and Conditional Calculations on them to make them show or not show. Regardless, you'll need to address this issue. The quickest way is to move this if portion higher up :

IF(GetFieldSelections(Product) = 'Vehicle/Home EQ' AND GetFieldSelections(Product) = 'Financial Card',

so that it occurs before the other one.

The other way to do it is to add an additional parameter for GetSelectedCount() to all your ifs. Like below

=IF(GetSelectedCount(Product) = 0, Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} CanFurnishings),

IF(GETSELECTEDCOUNT(Product)=1 and GetFieldSelections(Product) = 'Vehicle/Home EQ', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon)),

IF(GETSELECTEDCOUNT(Product)=1 and GetFieldSelections(Product) = 'Financial Card', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>}CanFurn_FinC),

IF(GETSELECTEDCOUNT(Product)=2 and GetFieldSelections(Product) = 'Vehicle/Home EQ' AND GetFieldSelections(Product) = 'Financial Card', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon +CanFurn_FinC))

))))

Re: Multiple Criteria with GetFieldSelections()

girirajsinh — Thu, 19 Jan 2017 19:42:52 GMT

Could you share the sample qvw?

Re: Multiple Criteria with GetFieldSelections()

Fri, 20 Jan 2017 06:22:56 GMT

Perhaps creating that value in the back end as a new field would solve the problem.

Good Luck,

Pravesh

Re: Multiple Criteria with GetFieldSelections()

lucasdavis500 — Fri, 20 Jan 2017 15:43:27 GMT

=IF(GetSelectedCount(Product) = 0, Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} CanFurnishings),

IF(GetSelectedCount(Product) = 1 AND GetFieldSelections(Product) = 'Vehicle/Home EQ', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon)),

IF(GetSelectedCount(Product) = 1 AND GetFieldSelections(Product) = 'Financial Card', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} CanFurn_FinC),

IF(GetSelectedCount(Product) = 2 AND GetFieldSelections(Product) = 'Vehicle/Home EQ' AND GetFieldSelections(Product) = 'Financial Card', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther + CanFurn_Heloc + CanFurn_Helon + CanFurn_FinC))

))))

This formula doesn't work, but If I change the last IF() to just GetSelectedCount(Product) = 2, 'my criteria', then the appropriate number is returned...

Re: Multiple Criteria with GetFieldSelections()

Anonymous — Thu, 26 Jan 2017 20:41:42 GMT

Sorry I missed the obvious. GetFieldSelections(Product) when there is more than one selection will return a comma separated list. So it will look like Financial Card,Vehicle/Home EQ as one value. You can't do an AND for each value separately the way you have it.

so it would need to be something like:

IF(GetFieldSelections(Product) = 'Financial Card, Vehicle/Home EQ', Only({<DateType = {'Created'}, CanonicalDate = {$(=vTwelveRolling)}>} (CanFurn_Auto + CanFurn_AutoOther+ CanFurn_Heloc + CanFurn_Helon + CanFurn_FinC))