topic Re: Re: Conditional 'aggr' function in QlikView

Conditional 'aggr' function

Wed, 20 Aug 2014 22:28:11 GMT

Hi All,

I have a scenario where I have to write conditional aggr function based on synthetic dimension values. OR in the same way I have to write if then else clause using multiple aggr function like this:

if(dim1='abc',

avg(aggr(sum(m1),dim2)),

avg(aggr(sum(m1),dim3))

)

but the second avg(aggr...) is not displaying value in chart. Is this Qlikview limitation to write only one aggr in a expression?

Thanks in advance,

Anosh Nathaniel

Re: Conditional 'aggr' function

JonnyPoole — Thu, 21 Aug 2014 01:03:45 GMT

Can you share your example where the 2nd (else) aggr is not calculating correctly ?

Re: Conditional 'aggr' function

jagan — Thu, 21 Aug 2014 02:44:46 GMT

Hi,

Try like this

if(ValueList('abc', 'def') = 'abc',

avg(aggr(sum(m1),dim2)),

avg(aggr(sum(m1),dim3))

)

WHERE ValueList('abc', 'def') is your synthetic dimension.

Regards,

Jagan.

Re: Re: Conditional 'aggr' function

Thu, 21 Aug 2014 17:43:15 GMT

Hi Jagan and Jonathan,

Thanks a lot for your reply.

PFA a eg QVW. I tried doing it with Valuelist as well as Data island. but it is not working in both of the scenario.

Regards,

Anosh

Re: Conditional 'aggr' function

jagan — Fri, 22 Aug 2014 02:43:16 GMT

Hi,

What is your expected output?

Regards,

Jagan.

Re: Conditional 'aggr' function

jagan — Fri, 22 Aug 2014 03:04:56 GMT

Hi,

Use this expression in your chart

pick(

match(ValueList('a','b','c')

,'a'

,'b'

,'c'

)

,sum([Sales]) / Count(Sales)

,sum(Sales)

,Stdev(Sales)

)

This works, if not then let me know what is your expected output.

Regards,

Jagan.