topic AVG and SUM functions reside on the same expression? in QlikView

AVG and SUM functions reside on the same expression?

Tue, 07 Aug 2012 09:56:32 GMT

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!

Re: AVG and SUM functions reside on the same expression?

Tue, 07 Aug 2012 11:28:45 GMT

check this app...

Re: AVG and SUM functions reside on the same expression?

chematos — Tue, 07 Aug 2012 17:42:38 GMT

try this:

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

Re: AVG and SUM functions reside on the same expression?

swuehl — Tue, 07 Aug 2012 19:02:54 GMT

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

=pick(

KPIID,

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

)

Re: AVG and SUM functions reside on the same expression?

Tue, 07 Aug 2012 19:21:42 GMT

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

Re: AVG and SUM functions reside on the same expression?

swuehl — Tue, 07 Aug 2012 19:26:36 GMT

Use

pick(

KPIID,

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

)

to make the concat indepent from selections.

Re: AVG and SUM functions reside on the same expression?

Wed, 08 Aug 2012 07:18:44 GMT

Thanks, guys.

The pick method worked like a charm. THANKS!