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.

[sunny_talwar]

Try this

IF(Only({1} _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))

)

[Anonymous]

Not working.....

Any help?

[sunny_talwar]

It would be helpful to know what is "Not working....." Can you share a sample to show the issue? I won't be able to offer much help without you giving some details

[Anonymous]

The first column is:

=IF(Only(_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))

)

The second one is:

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

In this scenary (without selections), the graph returns the following:

But, if I select Cancelled in [Supplier Decision], it returns:

And that´s because there is no data for Cancelled in months 201710 and 201711, but in Set analisys I have excluded that value.

The surprising thing is that if it works if I don't add the IF function (second column) so I think that the problem comes from how it evaluates the set analysis when you put it into de IF function

I hope I made myself clear.

Thanks in advance.

[sunny_talwar]

I asked you to add {1} within Only... did you try doing that?

=IF(Only({1} _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]

Yes Sunny and its not working

[sunny_talwar]

Do you mind create two new expressions for me (don't label them

1) Only({1} _Medida_Id_2)

2) Only(_Medida_Id_2)

and then share two snapshots: 1) with selection, 2) without selection

[Anonymous]

[sunny_talwar]

In your chart my friend... not in a text box object