Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

Calculated Condition - multiple criteria's

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

6 Replies
Gysbert_Wassenaar

Try something like:

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


talk is cheap, supply exceeds demand
zagzebski
Creator
Creator
Author

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

zagzebski
Creator
Creator
Author

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) 

Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Gysbert_Wassenaar

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))'}


talk is cheap, supply exceeds demand
JohnnyV
Contributor
Contributor

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