Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try something like:
if(alt(vRetained,2)<1, count({1<Year={'$(=only(Year))'},Month={'$(=only(Month))'},Product={'$(=only(Product))'}>}Product),0)
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
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)
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.
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))'}
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))