Set expression Based on selection of multibox where a particular value should move to specific variable

[shekhar_analyti]

Hi All ,

I have a scenario where in a Multibox I have four country , France , Germany , UK , US .

Below is a set expression

Sum({<Value=varible1,Field1={xyz}>}Sale)+
Sum({<Value=varible2>},Field2={xyz}Sale)+
Sum({<Value=varible3>},Field3={xyz},Sale)+
Sum({<Value=varible4>},Field4={xyz},Sale)

My Requirement :

SCENARIO 1 : Single Select

if User selects France from Multibox then , France should go to variable 1 and return me value , but at same time set expression containing other variables should force return me 0

if user selects Germany then , Germany should go go to variable 2 and return me value , but at same time set expression containing other variables should force return me 0

SCENARIO 2 : Multiple Select

if User selects France and Germany from Multibox then France should go to variable 1 and Germany should go go to variable 2 and return me their sum , but at same time set expression containing other variables 3 and 4 should force return me 0

If user selects all four countries then variable  1 should take France , variable 2 should take Germany , variable 3 should take UK , 4 US

if user select France and UK then 1 should take France and variable 3 should take UK  while expression with variable 2 and 4 should return forced 0

Based of selection in multibox values should move to respective variable .

[sunny_talwar]

So is this question more relating to getting the right value into the variable? Would you be able to share a sample?

[shekhar_analyti]

Yes ... Exactly .. Right value into variable ...  I am really sorry I cant share a sample , actually its based on live real data .

[sunny_talwar]

May be like this for Variable1

SubField(Concat(DISTINCT Country, '|'), '|', 1)

and so on....

[shekhar_analyti]

But will that force expression with other varible to yield 0 ? Actually that is also equally important and needed

[sunny_talwar]

It might be much easier if you can share a sample to show how this can work....

[jonathandienst]

Do you really need to do this with variables. I may not understand your requirements exactly, but it looks like the native QV functionality should be able handle them.

[sunny_talwar]

I agree

[Kushal_Chawda]

I am not sure, what you are trying to achieve. As QlikView's default nature, if you select any country, qlikview will give you results for that country, also if you select multiple country, qlikview will give you result for two selected country. This is what we call it as AQL.

But to your requirement, you can do something like below

Variable1

=if(wildmatch(Concat(DISTINCT '|'&Country&'|',','),'*|France|*'),Only({<Country={'France'}>}Country),0)

Variable2

=if(wildmatch(Concat(DISTINCT '|'&Country&'|',','),'*|Germany|*'),Only({<Country={'Germany'}>}Country),0)

Variable3

=if(wildmatch(Concat(DISTINCT '|'&Country&'|',','),'*|UK|*'),Only({<Country={'UK'}>}Country),0)

Variable4

=if(wildmatch(Concat(DISTINCT '|'&Country&'|',','),'*|US|*'),Only({<Country={'US'}>}Country),0)

Sum({<Value={'$(varible1)'},Field1={'xyz'}>}Sale)+

Sum({<Value={'$(varible2)'},Field1={'xyz'}>}Sale)+

Sum({<Value={'$(varible3)'},Field1={'xyz'}>}Sale)+

Sum({<Value={'$(varible4)'},Field1={'xyz'}>}Sale)

[shekhar_analyti]

Hi ,

Please find attached .

Basically i wanted is something like this ...
If france  is selected then it should give me count of records from field one whose value is less than 5 .
while others set with other variable should return 0 .
And if nothing is selected in country the it should return count of records

with value less than 5 in field 1 from France + value less than 5 in field 2 from Germany + value less than 5 in field 3 from UK + value less than 5 in field 4 from US

Please refer data in sheet "data"