Return pick match condiotional

[jsobrinho]

Hi guys,

I need to verify if each line in my pick match is zero it is zero I need to put N/A, else the value. There is another way to getting the return of pick match and set the N/A to zero values? or is better do this for each line, with if statement inside pick match?

[rubenmarin]

I don't see other way to do it, in particular if the expression to evaluate if it's below zero is different than the one to do when is over zero.

[jsobrinho]

I'm sorry, I put expression wrong, so to evaluate if it's below zero is equal 

Pick(
Match(Dim1,'A','B','C'),
if(
Sum({<Dim1={'A'} ,Num={2}>}Expression1)/
Sum({<Dim1={'A'} ,Num={10}>}Expression1)-1>0,
Sum({<Dim1={'A'} ,Num={2}>}Expression1)/
Sum({<Dim1={'A'} ,Num={10}>}Expression1)-1,'N/A')
,

if(
Sum({<Dim1={'B'} ,Num={2}>}Expression1)/
Sum({<Dim1={'B'} ,Num={10}>}Expression1)-1>0,
Sum({<Dim1={'B'} ,Num={2}>}Expression1)/
Sum({<Dim1={'B'} ,Num={10}>}Expression1)-1,'N/A')
,

if(
Sum({<Dim1={'C'} ,Num={2}>}Expression1)/
Sum({<Dim1={'C'} ,Num={10}>}Expression1)-1>0,
Sum({<Dim1={'C'} ,Num={2}>}Expression1)/
Sum({<Dim1={'C'} ,Num={10}>}Expression1)-1
,'N/A')

)

[rubenmarin · Accepted solution]

I don't see how to improve performance, using a variable can help with readibility and maintenance, also a parametrized variable can be used, ie:
SET vExpressionName = (Sum({<Dim1={'$1'} ,Num={2}>}Expression1)/
Sum({<Dim1={'$1'} ,Num={10}>}Expression1)-1);

Expression using the variable:
Pick(Match(Dim1,'A','B','C')
,if($(vExpressionName(A))>0
,$(vExpressionName(A))
,'N/A')
,if($(vExpressionName(B))>0
,$(vExpressionName(B))
,'N/A')
,if($(vExpressionName(C))>0
,$(vExpressionName(C))
,'N/A')
)

[jsobrinho]

Thank you,

Two questions:
1- What is different of creating a variable in the script by SET, and create in CTRL + ALT + V? I tried to create by CTRL + ALT + V, but I can't.

2- Can I pass variable as measure? like:
SET vExpressionName = (Sum({<Dim1={'$1'} ,Num={2}>} $2)/
Sum({<Dim1={'$1'} ,Num={10}>} $2)-1);

$2 = Expression1 or Expressions2

I tried but I can't.

[rubenmarin]

1- You should be able to create them using CTRL + ALT + V, also from an input box andfrom document properties -> Variables

2- Yes, you can pass variable as measure, note that commas are usd as parameter separator so you measure shouldn't have any comma, ie you can use this:
$(vExpressionName(A, Sales))

This expands as:
(Sum({<Dim1={'A'} ,Num={2}>} Sales)/
Sum({<Dim1={'A'} ,Num={10}>} Sales)-1)