# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## Need help with expression

Hi all!

Here is my problem: I have two dimensions, A and B, and I have data C: C=AxB (there is C value for each pair (A,B)). Also, I have a formula: KPI=A*1000/(C*10*B). So, I have to get A-values, for which KPI, calcuted with grouping by B field, is MAX.

I know how to calculate MAX KPI grouping by B field:

=aggr(max(aggr(A*1000/(C*9.81*B),A,B)),B),

but how to get B value?

Tags (2)
1 Solution

Accepted Solutions
Not applicable

## Re: Need help with expression

Are you looking for this?

Expression: Max(TOTAL <B> KPI)

8 Replies
Not applicable

## Re: Need help with expression

Can you provide some sample data with expected output to try out what you are looking for?

Not applicable

## Re: Need help with expression

Hi, Sunny T!

First thing I have to edit my question: " but how to get A value?"

Here is the data to load:

A*1000/(C*9.81*B) as KPI

INLINE

[A, B, C

20, 8, 312

20.5, 8, 321

21, 8, 330

20, 8.5, 294

20.5, 8.5, 300.5

21, 8.5, 307

20, 9, 275

20.5, 9, 282

21, 9, 289]

I decided to calculated KPI already in script.

So here is what we have (pivot table for better understanding). I want to get something like this:

newA newB

20      8

21     8,5

20      9

I need newA and newB for my linear diagram.

Not applicable

## Re: Need help with expression

Are you looking for this?

Expression: Max(TOTAL <B> KPI)

Not applicable

## Re: Need help with expression

Exactly!

Thank you very much!

Not applicable

## Re: Need help with expression

If not, please make clear what part of this topic you still want discussion on .

May you live in interesting times!
Not applicable

## Re: Need help with expression

Hi!

Thanks for help once more But I still can't build what I want..

My purpose is a linear diagram: horizontal axis(dimension) - B, and expression should return optimal A (A, where we have max(KPI) grouping by B).

Output for sample data I gave above mentioned to be like this (in numeric view):

B - 8, A - 20

B - 8.5, A - 21

B - 9, A - 20.

Is it possible?

Not applicable

## Re: Need help with expression

This?

Dimension: B

Expressions

1) =FirstSortedValue(A, -KPI)

2) =Max(KPI)

Not applicable

## Re: Need help with expression

Thank you! Now it works!

Community Browser