Variables in set Analysis

[Anonymous]

How can I use a variable in a set analysis expression?

[#2]

I just tried this and it seems to work: Sum({$<"$(variable)"={'value'}>} Sales).
So add double quotes around the variable.

[Anonymous]

Thank you so much for the reply, actually i am trying to have the variable not in the identifier but in the field value, in your example it will be the Sales

[Oleg_Troyansky]

if you want to replace the "expression" (e.g. the Sales) by a variable that holds a definition of your dynamic expression - you can do it, it has nothing to do with the Set Analysis itself - these are two separate parts of the overall aggregation.

For example:

Variable:

vFormula = '(Sales-Cost)'

Expression:

sum({Set Analysis Condition } $(vFormula) )

is identical to a "static expression:

sum({Set Analysis Condition } (Sales-Cost) )

I hope this is what you had in mind...

[Anonymous]

That exactly what I had in mind, but when I enter my variable, no values are returned, I get nothing just null. I tested my variable and it works if I do not combine it with the set analysis condition.

my example is :

sum

({1<OBJECT ={"17*"}>} $(Current_Year_Balance))

[#6]

So your variable $(Current_Year_Balance) contains a lot of values, but you only want to summarize the values where OBJECT = '17*' (you should use single quotes here btw. Better for performance). This means there must be a connection between the values in the variable and the values in OBJECT.

[burgersurfer]

So I have a formula that looks like this:

($(vSalesTY) - $(vReturnsTY)) / ($(vNetTransactionsTY))

Can I then add set analysis to the above calculation as follows, to get a result for a set timeperiod?

Sum( {$<FinTxtMonth = {'$(MonthSelected)'} >} (($(vSalesTY) - $(vReturnsTY)) / ($(vNetTransactionsTY))))

If not, how do I use set analysis on the original expression

[#8]

I have a similar query to this, are you able to use a variable as the value of the set analysis?

Count({<[Has Target] = {'Yes'},[SLX Relationship RM Name]-={[RM Selection]}>} DISTINCT [SLX Account ID])

In the above RM Selection is my variable would that work?

Thanks

[#9]

Hello!!!

Have trouble using a variable in set analysis...

This works fine (hard coding a date 12 months ago), I get a count of 14:

count({1<InceptionDate = {">=07/06/10"}>} PolicyNo)

But as this needs to be a rolling count I set up a variable:

v12MonthsStartDate = AddMonths(today(),-12)

Then replaced the date with the variable:

count({1<InceptionDate = {">= v12MonthStartDate"}>} PolicyNo)

but now I get a count of 0...

Any ideas why this isn;t working???

Thanks, Mike

[stevedark]

Hi Mike,

A couple of things, first up Set Analysis is sensitive to date formats, so I would suggest wrapping your variable assignment in a date function:

date(AddMonths(today(), -12), 'DD/MM/YY')

Also I would recommend using dollar sign expansion in the Set Analysis, like this:

count({1<InceptionDate = {">=$(v12MonthStartDate)"}>} PolicyNo)

Personally, I would also set up the variable in the load script (provided your load script runs after midnight on the day you are looking at data) so that you are putting in a fixed rather than a calculated value:

let v12MonthStartDate = date(AddMonths(today(), -12), 'DD/MM/YY');

This way you can check the date in the variable to ensure it is appearing as it is requried in the statement.

Hope that all makes sense.

Regards,

Steve