topic Re: Set analisys and if function in QlikView

Set analisys and if function

Anonymous — Thu, 09 Nov 2017 13:02:43 GMT

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.

Re: Set analisys and if function

sunny_talwar — Thu, 09 Nov 2017 13:09:21 GMT

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

)

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 08:29:29 GMT

Not working.....

Any help?

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 12:14:00 GMT

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

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 12:31:21 GMT

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.

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 12:33:50 GMT

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

)

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 12:37:01 GMT

Yes Sunny and its not working

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 12:39:01 GMT

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

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 12:41:38 GMT

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 12:42:55 GMT

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

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 12:43:05 GMT

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

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 12:44:16 GMT

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

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 12:46:35 GMT

No selection:

Selection:

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 13:01:17 GMT

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

)

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 13:01:43 GMT

Also, are you making selection in _Medida_Id_2 field?

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 13:14:03 GMT

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

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 13:19:51 GMT

Yup, this should work

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

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 13:20:28 GMT

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

)

Re: Set analisys and if function

Anonymous — Fri, 10 Nov 2017 13:23:37 GMT

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

Thaks for all Sunny.

Great support!!!!!!

Re: Set analisys and if function

sunny_talwar — Fri, 10 Nov 2017 13:25:32 GMT

Why not mark my response as correct gaizka_qv‌?