topic Re: Expression result based on two dimensions in QlikView

Expression result based on two dimensions

Sun, 09 Mar 2014 23:28:51 GMT

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

Re: Expression result based on two dimensions

Clever_Anjos — Mon, 10 Mar 2014 00:14:18 GMT

Is it possible to post a QVW?

Re: Expression result based on two dimensions

Mon, 10 Mar 2014 00:18:31 GMT

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

Re: Expression result based on two dimensions

srchilukoori — Mon, 10 Mar 2014 01:14:46 GMT

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

Re: Expression result based on two dimensions

Mon, 10 Mar 2014 01:29:54 GMT

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

Re: Expression result based on two dimensions

Mon, 10 Mar 2014 03:11:57 GMT

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

Re: Expression result based on two dimensions

Mon, 10 Mar 2014 04:24:57 GMT

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

Re: Re: Expression result based on two dimensions

chiru_thota — Mon, 10 Mar 2014 04:33:52 GMT

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

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

Re: Expression result based on two dimensions

bgerchikov — Mon, 10 Mar 2014 04:34:53 GMT

Hi,

If you want set analysis:

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