GetFieldSelections in Setanalysis

[#1]

Hi Community Experts,

Can I get the below expression fully in set analysis without using if condition.

IF(GetFieldSelections(Product,';')='AA;BB' or GetFieldSelections(SubProd,';')='AAA;BBB',SUM({$<ProdType-={'SmartPhone'}>}Amount),

  IF(GetFieldSelections(Product,';')='CC;DD' or GetFieldSelections(SubProd,';')='CCC;DDD',SUM({$<ProdType-={'Mobile'}>}Amount),

SUM({$<ProdType-={'SmartPhone','Mobile'}>}Amount)))

Thanks lot

[Oleg_Troyansky]

No, I don't think you can fit this condition in Set Analysis, or at least not without revealing more associations between Product, SubProd and ProdType. If I understand your expression, you are excluding different ProdTypes based on the field selections in Product and SubProd.

I'd try and build associations in the data that could drive the same behavior. It looks like some of those associations might be missing and you are trying to compensate by using IF and Set Analysis.

Having said that, this IF statement is not too bad, because it's outside of the SUM, and therefore it's only performed once per chart line.

[kumarnatarajan]

Hi,

I think if  condition not bad for your case.But you can able to store the excluding ProdTypes values  in variable based on your condition.

Ex:  vExclude="Your Condition";

=SUM({$<ProdType-={$(vExclude)}>}Amount)

[#4]

Hi All,

I have converted the expression into if condition fully. Its working fine in expression window. But if I substitute variable then I am always getting last condition true.

$(Variable)   if I write it in expression window  of chart its giving always last part of expression.

Variable=

sum(IF(GetFieldSelections(Product,';')='AA;BB' or GetFieldSelections(SubProd,';')='AAA;BBB' and ProdType<>'SmartPhone') or

(GetFieldSelections(Product,';')='CC;DD' or GetFieldSelections(SubProd,';')='CCC;DDD' and ProdType<>'Mobile') or

(ProdType<>'SmartPhone' and ProdType<>'Mobile'), GrasRevenueUSD))

[jagan]

Hi,

Try like this

Create a variable with the below expression

vExcludeValues
=IF(GetFieldSelections(Product,';')='AA;BB' or GetFieldSelections(SubProd,';')='AAA;BBB','SmartPhone',

  IF(GetFieldSelections(Product,';')='CC;DD' or GetFieldSelections(SubProd,';')='CCC;DDD','Mobile',

'SmartPhone,Mobile'))

Now use this variable value in set analysis expression.

=SUM({$<ProdType-={$(vExcludeValues)}>} Amount)

Regards,

Jagan.

[#6]

Hi Jagan,

Thanks for your reply.

The expression provided by you is not fulfils the required.

I want to have everything in a variable. In the expression window I want to have only variable name.

Like $(Variable)

I have solved the issue with if condition itself. But now the issue is to put in variable and to use.

Can you provide me the modified variable value accordingly.

[jagan · Accepted solution]

Hi,

Then in variable don't use =, then in expression box use like this

=$(VariableName)

If you use = in variable declaration then it evaluates the expression and you will get single value in expression,

Hope this helps you.

Regards,

Jagan.

[ilanbaruch]

hi,

I read your comment and loaded exclude value  to a variable (GetFieldSelections )

and then the variable into text box and its working, but

when loading into SA it is not working

SUM({$<(category)-={$(vExclude)}>}Quantity )

any ideas?

[jagan]

Hi,

Create a new thread and attach some sample file with your expected output.

Regards,

Jagan.

[ilanbaruch]

Hi,

Check out 'GetFieldSelections + SetAnalisys' in Qlik Community.