Expression result based on two dimensions

[#1]

Hi All

I'm having trouble with getting an appropriate result with this expression.

I have a table loaded with Selector, MinValue, MaxValue and Multiplier.

I am looking for an expression (set is my current slant) that will show me the Multiplier for a matched Selector (this is my key field), with value between the MinValue and MaxValue.

This one, doesn't work...it gives a total of all results in the Multiplier field, not the specific one that matches the value.

=Sum({1<[MinValue]={">=$(=[Value])"},[MaxValue]={"<=$(=[Value])"}>}[Multiplier)

I have tried Only, however I get no result.

Can anyone help please??

[Clever_Anjos]

Is it possible to post a QVW?

[#3]

I'm afraid not...I have had to rename fields due to sensitivity as it is.

[srchilukoori]

It would be easier to comprehend with an example (with a mock data set).

SC

[#5]

I'll try

I have a Value of 3

Selector     Min     max     Multiplier

1               1          5          15

1               6          10          30

using the table above, 3 is greater then 1 and less than 5 so the Multiplier should be 15

if the value was 7, then the multiplier should be 30

[#6 · Accepted solution]

I don't know the reason you are going for set analysis, but you can use an if statement as well, something like this

Sum(if(Value>=MinValue and Value<=MaxValue, Multiplier) )

Thanks,

Jyotsna

[#7]

Thanks Jyotsna, this has worked!!  I was over-complicating things it seems!!

[chiru_thota]

Change your expression as below.Have a look at attached QVW

=Sum({<Min = {'<=$(=$(Value))'},Max = {'>=$(=$(Value))'}>}Multiplier)

[bgerchikov]

Hi,

If you want set analysis:

=Sum({1<[MaxValue]={">=$(=only([Value]))"},[MinValue]={"<=$(=only([Value]))"}>}[Multiplier])