AVG and SUM functions reside on the same expression?

[#1]

Hi,

I have set of KPI definition that should be evaluated "on the fly" using dollar expansion and presented in straight table

SOURCE

KPI:
LOAD INLINE * [
[KPI ID], [KPI Definition]
1, 'AVG([KPI Value])'
2, 'AVG([KPI Value])'
3, 'SUM([KPI Value])' 
]
;
KPIValue:
LOAD [KPI ID]
         ,[KPI Value]
RESIDENT KPI_Value; 

Straight table:

Dimension:

- KPI ID

Expression:

$(=[KPI Definition])

Problem: calculation of the expression fails when it try to calculated KPI 3.

Any idea's how i can solve this?

Thanks in advance!

[#2]

check this app...

[chematos]

try this:

aggr($(=[KPI Definition]), [KPI ID])

[swuehl]

I think the only way to create a dynamic expression is to use a pick() / match () combination:

=pick(

KPIID,

$(=concat(KPIDefinition,','))

)

See also attached,

Stefan

[#5]

This works. but... If you select KPI 3, you'll end up with null. KPI 1 & 2 work fine

[swuehl · Accepted solution]

Use

pick(

KPIID,

$(=concat({1} KPIDefinition,','))

)

to make the concat indepent from selections.

edit: see also http://community.qlik.com/thread/59081

[#7]

Thanks, guys.

The pick method worked like a charm. THANKS!