Grouping in If Statement

[#1]

Is there an easy way on creating this formula

If(Aggr(sum(Field1),Field2) / Aggr(Sum(Field3),Field2) >=1.001 and Aggr(sum(Field1),Field2) / Aggr(Sum(Field3),Field2) <=1.100}, '1.001 - 1.100')

something like,

If(Aggr(sum(Field1),Field2) / Aggr(Sum(Field3),Field2) = {">=1.001 <=1.100"}, '1.001 - 1.100').

[ankitbisht01]

how many different fields are there ? or  there are  only two fields , one which is being calculated and  one for sorting. ?

if there are different  fields involved then  on the basis of that set analysis will be maid. let me know

Regards

Ankit Bisht

[jonathandienst]

Aggr(sum(Field),Field) = Sum(Field) or Sum(DISTINCT Field). The unnecessary Aggr() will add to the time taken to calculate the expression. And Aggr(sum(Field),Field) / Aggr(Sum(Field),Field) will always return 1.

I would try to do this sort of grouping/bucketting in the load script, but if you have to do it in the front end, then (assuming its a calculated dimension), for what group are you summing the values of Field?

[MK_QSL]

It may be you are missing TOTAL Qualifier.

If(Aggr(sum(Field),Field) / Aggr(Sum(TOTAL Field),Field)

[#5]

There are 3 fields,  sorry for the confusion, I already edited my post.

[#6]

no, I just want to create an if statement without repeating the same condition with the difference of >= and <=. there might be script that will not repeat the statement, just like in the set analysis which is condition = {>=value<=value}.

[Alvaro_Palacios]

Hi Royce,

I'd make such operations in the script and avoid calculating aggr() within an if statement. Using aggr generally leads to increased chart calculation time.

CalculatedFields:

Load Field2,

        if( sum(Field1)/sum(Field3) >= num(1.001) and sum(Field1)/sum(Field3) <= num(1.100), '1.001 - 1.100')

From Table

Group by Field2

Regards,

Alvaro P.

[sasiparupudi1]

you could may be put your expression Aggr(sum(Field1),Field2) / Aggr(Sum(Field3),Field2) in a variable

and use it in the If condition.

if(($variable)>1.001 and ($variable)<=1.100,'1.001 - 1.100')

you can not mix set analysis syntax in the if condition as below

If(Aggr(sum(Field1),Field2) / Aggr(Sum(Field3),Field2) = {">=1.001 <=1.100"}, '1.001 - 1.100').

hth

Sasi

[ankitbisht01]

it can be cut short something like this :

If(Aggr ((sum(Field1) / Sum(Field3)),Field2) >=1.001 and Aggr((sum(Field1) / Sum(Field3)),Field2) <=1.100}, '1.001 -1.100').

But where what would you like to write in else ? , and where you want to use this script , backend or fornt end.?

Regards

Ankit Bisht