Multiple Criteria with GetFieldSelections()

[lucasdavis500]

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

[Anonymous]

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

))))

[girirajsinh]

Could you share the sample qvw?

[#4 · Accepted solution]

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

Good Luck,

Pravesh

[lucasdavis500]

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

[Anonymous]

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