how to write a set analysis with a variable in it

[jim_chan]

Hi all, 

I have a front expression i need to edit. ok i have a line chart, need to use the below expression. This line chart, has a condition, user only want to see Store 2 - Avg Sales Performance Trend.

the tricky part here is for some security reason - cant see the script in this variable - vAdhocExpression_X.

But due to this requirement of this chart, it must only display STORE_NO = 2.

So i wrote the below script, resulted no result out, just a straight line, means not correct script. 

if(STORE_NO = '2',$(=$(vAdhocExpression_X(1)))/count(distinct Date1))

Please help guys! 

[vinieme12]

You can view Variable Definition in Expression Editor

But Why are you trying to use a variable without knowing it's definition?

why not use something like below

sum({<STORE_NO={'2'}>}Sales)/Count(Distinct Date1) 

replace "Sales"  with your actual sales field

[jim_chan]

Nope. Due to some reasons. Sorry, so is there a way I could this condition
with together with this variable ?

[vinieme12]

can you post the variable definition? 

We need to know the variable definition so that we know the order to place each parameter

[jim_chan]

It's a sum sales set analysis and with other conditions as well. 

I got the correct avg sales by using $(=$(vAdhocExpression_X(1)))/count(distinct Date1))

But now, I need to add 1 more condition in it, which is store_no = 2

So , can I still write a condition in this script ? 

$(=$(vAdhocExpression_X(1)))/count(distinct Date1))

[vinieme12]

it depends entirely on the variable definition

We need to know if the variable is defined to accommodate multiple parameters, I cannot help you with knowing the variable definition

[jim_chan]

It is Defined to accommodate multiple parameters,  except this one - store_no. It was use as a general avg sales variable. However new requirement,  user would want  to view line chart by specific store.  So, I m thinking is there a way we can write I  this script? 

$(=$(vAdhocExpression_X(1)))/count(distinct Date1))

[marcus_sommer · Accepted solution]

If the variable-expression ignored selections on STORE_NO or calculates anything on a total-level you couldn't implement any additional condition - otherwise you could use the mentioned if-loop (but querying a number and not a string) or you may include the condition within the count like:

... / Count({<STORE_NO={2}>} Distinct Date1) 

which would cause a division through zero by different stores which ends in NULL.

Beside of this I would tend to not using this variable else creating a new expression which fulfills the purpose.

- Marcus

[jim_chan]

Thanks , will use the other way round . Thank you all.