Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

malikaouenza
New Contributor

Variables in set Analysis

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

Tags (1)
10 Replies
Not applicable

Variables in set Analysis

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

malikaouenza
New Contributor

Variables in set Analysis

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

MVP
MVP

Variables in set Analysis

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...

malikaouenza
New Contributor

Variables in set Analysis

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))

Not applicable

Variables in set Analysis

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
New Contributor II

Variables in set Analysis

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

Not applicable

Variables in set Analysis

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

Not applicable

Re: Variables in set Analysis

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

MVP
MVP

Re: Variables in set Analysis

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

Community Browser