condition if in dimention

[#1]

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.

[#2]

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

[Gysbert_Wassenaar]

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)

[#4]

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

[#5]

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