topic Re: Set analysis question in QlikView

Set analysis question

Wed, 18 Dec 2013 04:27:29 GMT

hi , how can i convert this ‘if’ statement into ‘Set Analysis’: (sum({$<segfuerza={'P1'}>}visitc))

sum(visitc)/ if(Pais= 'BH',
if(TIPO_Cliente = 'PRES',
if(Fuerza <>'SP',124,
   if(Fuerza='SP',95,
   if(Region='WI',
   if(TIPO_Cliente='PRES',SUM(Vp))))),sum(Vp)),

if(Pais= 'BB',
if(TIPO_Cliente = 'PRES',
if(Fuerza <>'SP',124,
   if(Fuerza='SP',95,
   if(Region='WI',
   if(TIPO_Cliente='PRES',SUM(Vp))))),sum(Vp)),

if(Pais= 'AC',
if(TIPO_Cliente = 'PRES',
if(Fuerza <>'SP',114,
   if(Fuerza='SP',95,
   if(Region='WI',
   if(TIPO_Cliente='PRES',SUM(Vp))))),sum(Vp)),

if(Pais= 'JA',
if(TIPO_Cliente = 'PRES',
if(Fuerza <>'SP',152,
   if(Fuerza='SP',133,
if(Region='WI',
   if(TIPO_Cliente='PRES',SUM(Vp))))),sum(Vp)),

if(Pais= 'TT',
if(TIPO_Cliente = 'PRES',
if(Fuerza <>'SP',152,
   if(Fuerza='SP',133,
   if(Region='WI',
   if(TIPO_Cliente='PRES',SUM(Vp))))),sum(Vp)),

   sum(Vp))))))

Re: Set analysis question

Gysbert_Wassenaar — Wed, 18 Dec 2013 07:10:53 GMT

That expression cannot be changed into a set analysis expression. Your expression doesn't just filter values, but contains a lot of branches that each return a different result. That kind of logic can't be done with set analysis. Also an if(...some_condition...,sum(...some_thing...)) is not necessarily the same as a sum(if(...some_condition...,...something...)).

Re: Set analysis question

Wed, 18 Dec 2013 07:14:35 GMT

whats the difference?

Re: Set analysis question

Gysbert_Wassenaar — Wed, 18 Dec 2013 07:27:42 GMT

For example, with if(sum(A)>B,sum(A)) you first calculate the sum of values and then do a comparison of the sum with something, with sum(if(A>B,A)) you first do a comparison of the values before they are summed and only then do a sum.

Re: Set analysis question

Wed, 18 Dec 2013 07:46:17 GMT

Gysbert Wassenaar is right, your expression contains lots of branches.

But you can surely reduce these branches by taking out the common parts and then using set analysis.

I figured out that : sum({<TIPO_Cliente = {"PRES"},Region = {"WI"}>}Vp)

this is common to all branches. So you can surely improve your expression.

Thanks,

Angad