Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for
Did you mean:
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?

1 Solution

Accepted Solutions
MVP

Are you looking for this?

Expression: Max(TOTAL <B> KPI)

8 Replies
MVP

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

Not applicable
Author

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.

MVP

Are you looking for this?

Expression: Max(TOTAL <B> KPI)

Not applicable
Author

Exactly!

Thank you very much!

Master III

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

May you live in interesting times!
Not applicable
Author

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?

MVP

This?

Dimension: B

Expressions

1) =FirstSortedValue(A, -KPI)

2) =Max(KPI)

Not applicable
Author

Thank you! Now it works!

Community Browser