Set analisys and if function

[Anonymous]

Hi,

I have an expression:

IF(_Medida_Id_2=4,

RangeSUM(Above(count({<[Year - Month]=,Supplier={"$(vL.Supplier1)"},[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}distinct id_NC),0,3))/

RangeSum(Above(sum({<[Year - Month]=,Supplier={"$(vL.Supplier1)"},[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}[Purchased €(Comp.Currency)]),0,3))

)

if I select 'Cancelled' value in [Supplier Decision] field the expression doesnt work. It´s only considering the [Year - Month] values for 'Cancelled'

But, if I define the expression as:

RangeSUM(Above(count({<[Year - Month]=,Supplier={"$(vL.Supplier1)"},[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}distinct id_NC),0,3))/

RangeSum(Above(sum({<[Year - Month]=,Supplier={"$(vL.Supplier1)"},[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}[Purchased €(Comp.Currency)]),0,3))

without the conditional, it works fine.

It seems that its not evaluating something in the set expression when its a part of the if function.

Does anybody knows could be happening?

Thanks in advanced.

[Anonymous]

_Medida_Id_2 it is not joinedto the model. It's an island table

[sunny_talwar]

I understand and that should not be a problem... all I want you to do is to add the above expressions in your chart above without labels

[Anonymous]

• No selection:

• Selection:

[sunny_talwar]

Try this out

=IF(Only({<[Supplier Decision]>} _Medida_Id_2)=4,

RangeSUM(Above(count({<[Year - Month]=,[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}distinct id_NC),0,3))/

RangeSum(Above(sum({<[Year - Month]=,[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}[Purchased €(Comp.Currency)]),0,3))

)

[sunny_talwar]

Also, are you making selection in _Medida_Id_2  field?

[Anonymous]

Its working avoiding the selections in [Supplier Decision], but I have noticed that the problem also happens with the other field in the set analisys ([Cause Responsible])

Is there any way to avoid selections in those fields ([Supplier Decision], [Cause Responsible])?

I mean, something like IF(Only({<[Supplier Decision], [Cause Responsible]>} _Medida_Id_2)=4

Thank you very much Sunny

[sunny_talwar]

Yup, this should work

IF(Only({<[Supplier Decision], [Cause Responsible]>} _Medida_Id_2)=4

[sunny_talwar · Accepted solution]

=IF(Only({<[Supplier Decision], [Cause Responsible]>} _Medida_Id_2)=4,

RangeSUM(Above(count({<[Year - Month]=,[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}distinct id_NC),0,3))/

RangeSum(Above(sum({<[Year - Month]=,[Cause Responsible]={"Supplier"},[Supplier Decision]=-{"Cancelled"}>}[Purchased €(Comp.Currency)]),0,3))

)

[Anonymous]

Only({<[Supplier Decision],[Cause Responsible]={"Supplier"}>}_Medida_Id_2)=4

Thaks for all Sunny.

Great support!!!!!!

[sunny_talwar]

Why not mark my response as correct gaizka_qv‌?