topic Re: Grouping in If Statement in QlikView

Grouping in If Statement

Mon, 17 Aug 2015 09:38:37 GMT

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

Re: Grouping in If Statement

ankitbisht01 — Mon, 17 Aug 2015 09:47:00 GMT

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

Re: Grouping in If Statement

jonathandienst — Mon, 17 Aug 2015 09:54:21 GMT

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?

Re: Grouping in If Statement

MK_QSL — Mon, 17 Aug 2015 09:56:15 GMT

It may be you are missing TOTAL Qualifier.

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

Re: Grouping in If Statement

Mon, 17 Aug 2015 09:59:15 GMT

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

Re: Grouping in If Statement

Mon, 17 Aug 2015 10:02:28 GMT

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}.

Re: Grouping in If Statement

Alvaro_Palacios — Mon, 17 Aug 2015 10:09:27 GMT

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.

Re: Grouping in If Statement

sasiparupudi1 — Mon, 17 Aug 2015 10:27:27 GMT

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

Re: Grouping in If Statement

ankitbisht01 — Mon, 17 Aug 2015 10:33:44 GMT

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