topic IF / AGGR / OR function into Set Analysis in QlikView

IF / AGGR / OR function into Set Analysis

Wed, 05 Jan 2011 09:22:05 GMT

Hi,

Can someone please help me out to change the below function using set analysis.

SUM(AGGR((IF(A='1' OR B='1' OR C='1',SALES)), OP, PER)) /
SUM(AGGR((IF(A='1' OR B='1' OR C='1',VOLUME)), OP, PER)))

Thanks

IF / AGGR / OR function into Set Analysis

Wed, 05 Jan 2011 19:08:25 GMT

If anyone has some suggestions, please let me know.

IF / AGGR / OR function into Set Analysis

johnw — Thu, 06 Jan 2011 01:14:40 GMT

Maybe this, but I'm not very confident in it without some data to play with:

sum(aggr(only({<A={'1'}>+<B={'1'}>+<C={'1'}>} SALES ),OP,PER))
/sum(aggr(only({<A={'1'}>+<B={'1'}>+<C={'1'}>} VOLUME),OP,PER))

It might even require something like this:

sum({<A={'1'}>+<B={'1'}>+<C={'1'}>} aggr(only({<A={'1'}>+<B={'1'}>+<C={'1'}>} SALES ),OP,PER))
/sum({<A={'1'}>+<B={'1'}>+<C={'1'}>} aggr(only({<A={'1'}>+<B={'1'}>+<C={'1'}>} VOLUME),OP,PER))

IF / AGGR / OR function into Set Analysis

Thu, 06 Jan 2011 10:14:31 GMT

Thanks John. You code was excellent and made our day.

I was looking at the forum for long time and didn't find any solution with aggr / or combination.

Thanks.for your time and appreciate your help.

IF / AGGR / OR function into Set Analysis

johnw — Thu, 06 Jan 2011 19:28:09 GMT

Out of curiousity and to help others who may happen upon this thread, which version of the code worked, or did it take some further modification?

IF / AGGR / OR function into Set Analysis

Fri, 07 Jan 2011 11:23:28 GMT

Your first expression worked as expected.

sum(aggr(only({<A={'1'}>+<B={'1'}>+<C={'1'}>} SALES ),OP,PER))
/sum(aggr(only({<A={'1'}>+<B={'1'}>+<C={'1'}>} VOLUME),OP,PER))

Thanks.