8 Replies Latest reply: May 5, 2016 6:45 AM by Мария Кияшко

# 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?

• ###### Re: Need help with expression

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

• ###### 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.

• ###### Re: Need help with expression

Are you looking for this?

Expression: Max(TOTAL <B> KPI)

• ###### Re: Need help with expression

Exactly!

Thank you very much!

• ###### Re: Need help with expression

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

• ###### 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?