Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Not applicable

Expression result based on two dimensions

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

8 Replies
Employee

Is it possible to post a QVW?

Not applicable
Author

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

Specialist

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

SC

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Specialist

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

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

Partner - Creator III

Hi,

If you want set analysis:

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

Community Browser