Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a single bar in a bar chart that uses a variable with a stored expression in the expression field. I need to restrict the user selections to only 3 filters so that if any filters other than those are clicked, the expression = 0.
So the logic is if(SubRegion selected or Quarter selected or GBU selected, $(vTarget), 0)
I also need it to calculate vTarget if no fields are selected. There are many fields I want left out so I would prefer not to use {<field1=,field2=, etc>} if I can avoid it.
The problem I've run into is that all the examples I've come across to do this use sum() and when I do that the $(vTarget) returns the wrong number. I have tried using DISTINCT($(vTarget) but again, the number returns wrong. is there a way to specify exclusions (or inclusions) using my variable without using Sum()?
The X axis is a series of expressions.
When I set vTarget to zero the bar placeholder stays there and displays zero (I have those unchecked) hence the 0.0 for the Pre-Pipeline bar above still being visible. I was able to set it with one filter with
=if(isnull(field1),$(vTarget),0)
and it works great - zeroes out when that field is selected but still displays the placeholder. However, when I tried to add additional filters if(isnull(field1) or isnull(field2) etc... ( I also tried nesting ifs) it doesn't work properly.
so I thought this through but I'm not sure it would work because I need only the one bar to have this condition, not the entire chart. I don't believe you can set one bar of a chart to an alternate state, but even if I could, I would still need the other bars to react to all the data, including those three fields and I don't believe you can set more that one alternate state at a time.
Thank you for weighing in.
I tried this and it did not work unfortunately, I got 0 for all combinations of filters, including the three I need.
I was able to figure this out
=if((getSelectedCount(field1)>0
or getSelectedCount(field2)>0
or getSelectedCount(field3)>0
..etc...),0,$vTarget))
Unfortunately I had to list several field names but it is working perfectly. Thank you for all your suggestions!