topic Re: condition if in dimention in QlikView

condition if in dimention

Wed, 19 Dec 2012 07:50:58 GMT

Hi!

I have expression like this:

sum( if(aggr(sum({<bg_t={'*'}, bg_t-={'б\г'} >}Quantity),AffiliateID,Holding,mes,mes_a )>0

and mes = (if(right(mes_a,2)-1>0, mes_a -1, left(mes_a,4)-1&12))

,1,0))

and it's working good

I need move condition after "and" into dimention. Unfortunatly construction like this:

sum( if(aggr(sum({<bg_t={'*'}, bg_t-={'б\г'}, mes = (if(right(mes_a,2)-1>0, mes_a -1, left(mes_a,4)-1&12)) >}Quantity),AffiliateID,Holding,mes,mes_a )>0

,1,0))

not working

what i missed? Please help.

Re: condition if in dimention

Wed, 19 Dec 2012 07:53:23 GMT

sorry... i take off cirilic simbol:

sum( if(aggr(sum({<bg_t={'*'}>}Quantity),AffiliateID,Holding,mes,mes_a )>0

and mes = (if(right(mes_a,2)-1>0, mes_a -1, left(mes_a,4)-1&12))

,1,0))

this working

sum( if(aggr(sum({<bg_t={'*'}, mes = (if(right(mes_a,2)-1>0, mes_a -1, left(mes_a,4)-1&12)) >}Quantity),AffiliateID,Holding,mes,mes_a )>0

,1,0))

this is not working

Re: condition if in dimention

Gysbert_Wassenaar — Wed, 19 Dec 2012 08:12:50 GMT

It looks like your if condition for mes is calculated per row, not for the entire chart at once. If that's true then you can't put the condition in the set analysis expression. But the syntax isn't correct too. You could try using a variable, e.g. v_Mes = if(right(mes_a,2)-1>0, mes_a -1, left(mes_a,4)-1&12) and use the variable in the set analysis expression sum({<bg_t={'*'}, mes = {'$(v_Mes)'} >}Quantity)

Re: condition if in dimention

Wed, 19 Dec 2012 08:37:18 GMT

Hi, Gysbert! Thank you for help.

"It looks like your if condition for mes is calculated per row" - yes it is. This formila:

sum( if(aggr(sum({<bg_t={'*'}>}Quantity),AffiliateID,Holding,mes,mes_a )>0

and mes = (if(right(mes_a,2)-1>0, mes_a -1, left(mes_a,4)-1&12))

,1,0))

give me sum of Quantity for previous month. mes_a and mes i take from 2 different tables without any joins.

My table:

city	mes_a	mes_a-1	active month	previous month
Moscow	201201	201112	313	477
Moscow	201202	201201	328	313
Moscow	201203	201202	363	328

and now i need count such condition:

1. in active month Quantity > 0

2. in previos month Quantity = 0

i try this:

sum( if(aggr(sum({<bg_t={'*'}>}Quantity),AffiliateID,Holding,mes,mes_a )>0

and mes = mes_a

, if(aggr(sum({<bg_t={'*'}>}Quantity),AffiliateID,Holding,mes,mes_a )=0

and mes = (if(right(mes_a,2)-1>0, mes_a -1, left(mes_a,4)-1&12)), 1

,0))

)

but it is not works

Re: condition if in dimention

Wed, 19 Dec 2012 09:49:45 GMT

in theory ...why it't the same?

sum( if(aggr(sum({<bg_t={'*'} >}Quantity),AffiliateID,Holding,mes,mes_a )>0
and mes = mes_a
,1,0))

works

sum(if(aggr(sum({<bg_t={'*'}}, mes = mes_a >}Quantity),AffiliateID,Holding,mes,mes_a )>0 ,1,0))

not works

i think it must be another topic