topic Variables in set Analysis in QlikView

Variables in set Analysis

Anonymous — Fri, 05 Mar 2010 16:24:48 GMT

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

Variables in set Analysis

Fri, 05 Mar 2010 17:30:13 GMT

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

Variables in set Analysis

Anonymous — Fri, 05 Mar 2010 19:02:00 GMT

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

Variables in set Analysis

Oleg_Troyansky — Sun, 07 Mar 2010 00:35:46 GMT

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

Variables in set Analysis

Anonymous — Mon, 08 Mar 2010 14:22:28 GMT

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

Variables in set Analysis

Thu, 11 Mar 2010 16:06:10 GMT

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.

Variables in set Analysis

burgersurfer — Fri, 23 Jul 2010 12:02:35 GMT

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

Variables in set Analysis

Fri, 22 Oct 2010 11:17:01 GMT

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

Re: Variables in set Analysis

Tue, 07 Jun 2011 10:03:15 GMT

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

Re: Variables in set Analysis

stevedark — Tue, 28 Jun 2011 17:14:10 GMT

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

Re: Variables in set Analysis

Mon, 18 Jul 2011 22:06:04 GMT

Oleg (and everyone)-

This is wonderfully helpful. That being said, how do I set up set analysis (with variables) to sum up specific values contained in the output of the variables? Here's an example expression:

=sum({<Product.ProdName={'$(vProd1)'}>}
Product.ProdValue) + sum({<Product.ProdName={'$(vProd2)'}>}
Product.ProdValue) + sum({<Product.ProdName={'$(vProd3)'}>}
Product.ProdValue) + sum({<Product.ProdName={'$(vProd4)'}>}
Product.ProdValue)

The above expression works absolutely fine. I need a totals column. That being said, the values contained in vProd1, vProd2, (so on...) contain either a "ProdA" or "ProdB" contained in the ProductName (which is pulled in based on the variable). I need to sum up all values containing "ProdA" and sum up all values containing "ProdB" each in it's own column. What's the best way of accomplishing this?

Also... I have to imagine this can be highly intensive (or could be). I've provided a highly shorted version of this expression above. Additionally, there are always hundreds of Products being added everyday. So, I don't want to run into an "Out of memory" situation. So also looking for efficient ways of handling this situation as well.

Thanks!