Need help with expression

[#1]

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?

[sunny_talwar]

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

[#3]

Hi, Sunny T!

Thak you for your attention!

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

Here is the data to load:

LOAD A, B, C,

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.

[sunny_talwar · Accepted solution]

Are you looking for this?

Expression: Max(TOTAL <B> KPI)

[#5]

Exactly!

Thank you very much!

[oknotsen]

If your question is now answered, please flag the Correct Answer and possible Helpful Answers.

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

[#7]

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?

[sunny_talwar]

This?

Dimension: B

Expressions

1) =FirstSortedValue(A, -KPI)

2) =Max(KPI)

[#9]

Thank you! Now it works!