Conditional 'aggr' function

[#1]

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

[JonnyPoole]

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

[jagan]

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.

[#4]

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

[jagan]

Hi,

What is your expected output?

Regards,

Jagan.

[jagan]

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.