topic set analysis with sum(if()) in the script in QlikView

set analysis with sum(if()) in the script

Thu, 31 Jan 2013 16:36:36 GMT

Dear all,

I have a set analayis in a chart which is computing:

if the AVG of the months 1 to 3 is bigger than 1.1*AVG of months 4 to 6, the AVG of months 1 to 3 is taken into consideration, otherwise the AVG of months 1 to 6.

the set analysis looks like follow:

if((Sum({$<BUCKET={">=-3 <=-1"}>} BucketQty)/3) > 1.01*(Sum({$<BUCKET={">=-6 <=-4"}>} BucketQty)/3),
          (Sum({$<BUCKET={">=-3 <=-1"}>} BucketQty)/3),
                    (Sum({$<BUCKET={">=-6 <=-1"}>} BucketQty)/6))

Now,I am trying to do this in-script for different reasons.

I tried the approach with LET, i.e.

LET vTrend=if((Sum({$<BUCKET={">=-3 <=-1"}>} BucketQty)/3) > [...]

I tried the approach of SUM(If(condition, value))

but still not working

if(
Sum(if( BUCKET>=(-3) and BUCKET<=(-1)), BucketQty/3) > 1.01*(Sum(if(BUCKET>=(-6) and BUCKET<=(-4)), BucketQty/3)),
Sum(if(BUCKET>=-3 and BUCKET <=-1), BucketQty/3),
Sum(BUCKET>=-6 and BUCKET <=-1), BucketQty/6) as CalcTrend;

Is it thus possible to achieve that set anallysis above mentioned, in the script?

Many thanks

Re: set analysis with sum(if()) in the script

Thu, 31 Jan 2013 16:41:23 GMT

Hi,

you have to use this syntax in script langiage

(if..then, elseif..then, else end if)

and not:

if(condition,'','')

Hope it helps

Regards

Giampiero

Re: set analysis with sum(if()) in the script

Thu, 31 Jan 2013 16:45:36 GMT

Sorry,

if you're calculating for fields you're right.

Re: set analysis with sum(if()) in the script

Thu, 31 Jan 2013 16:58:38 GMT

giampiero.cina wrote:
Sorry,
if you're calculating for fields you're right.

nice

but then it is still not working!

forgot to mention, this table is made of thousands of rows, where each one of them is an item.

For each item there are 12 buckets, i basically to have a further field being the weighted average described as above.

Re: set analysis with sum(if()) in the script

Thu, 31 Jan 2013 17:00:23 GMT

Hi,

Have you tried in this way :

if (if(BUCKET=>=-3 and BUCKET<=-1,sum(BucketQty)/3) > if(BUCKET=>=-6 and BUCKET<=-4,sum(BucketQty)/3) * 1.01, if(BUCKET=>=-3 and BUCKET<=-1,sum(BucketQty)/3), if(BUCKET=>=-6 and BUCKET<=-1,sum(BucketQty)/6)) as CalcTrend;

Hope it helps

Regards

Giampiero

Re: set analysis with sum(if()) in the script

Thu, 31 Jan 2013 17:23:35 GMT

I needed to change a bit your script to have the colours appear in the script.

(namely, from

BUCKET=>=-3

BUCKET>=-3

and so on...)

But I got the following error when reloading

Invalid expression
BucketDemand:
LOAD *,
if (if(BUCKET>=-3 and BUCKET<=-1,sum(BucketQty)/3) > if(BUCKET>=-6 and BUCKET<=-4,sum(BucketQty)/3) * 1.01,
if(BUCKET>=-3 and BUCKET<=-1,sum(BucketQty)/3),
if(BUCKET>=-6 and BUCKET<=-1,sum(BucketQty)/6)) as CalcTrend
RESIDENT BucketDemandTemp

so still not good....

Re: set analysis with sum(if()) in the script

Fri, 01 Feb 2013 09:11:40 GMT

Hi,

could you post a qvw to see how to help you ?

Regards

Giampiero

Re: set analysis with sum(if()) in the script

Fri, 01 Feb 2013 10:07:06 GMT

Hi,

I think the problem now is not the IF command but using the Sum function you have to use the group by.

I attach a simple example to understand:

LOAD Id,

FieldA,

Sum(FieldB) as SumFieldB,

FieldC

FROM

(ooxml, embedded labels, table is test)

group by Id,FieldA,FieldC;

group by is obligatory.

I suppose that adding group by in your table you could solve your problem.

Hope it helps

Regards

Giampiero