topic Re: Calculated Condition - multiple criteria's in QlikView

Calculated Condition - multiple criteria's

zagzebski — Fri, 25 Oct 2013 21:23:33 GMT

Fields:

Year

Product
Month

VRetained

I have a chart that I need to have a calculated condition on.

The calculated condition has to use the fields above and address the following:

1. Year, Month and Product must have a selection BUT only 1

2. The variable vRetained must have an amount and it must be something less that 100%

3. There can be NO OTHER selections made from any of the other fields

Are the 3 critieria above possible using the Get function? I am struggling...

Lastly - I doubt this is possible - but can the error message tell the user which of the above are not met.

Thanks in advance for any help,

Steve

Re: Calculated Condition - multiple criteria's

Gysbert_Wassenaar — Sat, 26 Oct 2013 08:14:16 GMT

Try something like:

if(alt(vRetained,2)<1, count({1<Year={'$(=only(Year))'},Month={'$(=only(Month))'},Product={'$(=only(Product))'}>}Product),0)

Re: Calculated Condition - multiple criteria's

zagzebski — Sat, 26 Oct 2013 15:24:28 GMT

Thanks this works perfect except for one thing...

Is there anyway to build into the condition that a user can't select any field except Year, Month, Product, and vRetained?

For example if the user selects a "Region" in the example file than they would also get an error message?

Thanks,

Steve

Re: Calculated Condition - multiple criteria's

zagzebski — Sat, 26 Oct 2013 16:04:04 GMT

One more follow up! If I don't want the criteria of having to select one (1) product how would I change this expression?

if(alt(vRetained,2)<1, count({1<Year={'$(=only(Year))'},Month={'$(=only(Month))'},Product={'$(=only(Product))'}>}Product),0)

I tried this but didn't work:

if(alt(vRetained,2)<1, count({1<Year={'$(=only(Year))'},Month={'$(=only(Month))'}}>},0)

Re: Re: Calculated Condition - multiple criteria's

Gysbert_Wassenaar — Sun, 27 Oct 2013 14:37:30 GMT

The easiest way is to use the same filter as in the conditional expression in the chart expression(s) too. In the attached example I've put the filter in a variable vFilter and use that in both expressions. This way only selections in Year, Month and Product effect the chart.

If you don't need to be able to select more than one value in the listboxes for example for other charts then you can also enable the option Always One Selected Value in properties windows of the listboxes. First select a value, then enable that option on the General tab.

Re: Calculated Condition - multiple criteria's

Gysbert_Wassenaar — Sun, 27 Oct 2013 14:48:50 GMT

I tried this but didn't work:

if(alt(vRetained,2)<1, count({1<Year={'$(=only(Year))'},Month={'$(=only(Month))'}}>},0)

There's a curly brace too many in your expression: {'$(=only(Month))'}} should be {'$(=only(Month))'}

Re: Calculated Condition - multiple criteria's

JohnnyV — Wed, 13 Nov 2019 17:02:44 GMT

I have this expression and I am trying to implement the conditions in my comment section where by I am essentially converting a value to text - is alt the right function here in this case - need some direction on how to implement when multiple values will need to get converted to text?

If(RowNo() = 0, Avg(Aggr(Avg({<CagrEPS2Y={">-.6 <.6"}, // If field result is out of this range then convert value to "NM"
EpsYearplus3={">0"}, // If this field is negative or Null then convert result to "NA"
EpsNY={">0"}>}, //If this field is negative or Null then convert result to "NA"
CagrEPS2Y),OwnerName)),

Avg({<CagrEPS2Y={">-.6 <.6"},EpsYearplus3={">=0"}, EpsNY={">=0"}>}CagrEPS2Y))