topic Variable in Set Analysis in QlikView

Variable in Set Analysis

darrellbutler — Tue, 15 Mar 2011 16:41:41 GMT

Hi,

As a newbie can somebody point me in the right direction.

I have created the following variable:

LET

vMaxMonthYr = Month(Today(0)) & '-' &right(year(Today(0)),4);

I then use this in a set expression:

sum({$<[MonthYear] = {$('vMaxMonthYr')}, [TYPE] = {"COLL"}, [COMPANY_CODE] = {"0017"} > } [VOL])/1000000

However, the sum calculation doesnt work.

MonthYear is derived from my master calendar and has been calculated using the same formula as the variable.

Any thoughts ?

Kind Regards

Darrell

Variable in Set Analysis

Miguel_Angel_Baeyens — Tue, 15 Mar 2011 16:44:03 GMT

Hello Darrell,

You were almost there. Take a look at the quoting in the variable:

=sum({$<[MonthYear] = {'$(vMaxMonthYr)'}, [TYPE] = {'COLL'}, [COMPANY_CODE] = {'0017'} > } [VOL])/1000000

Hope that helps.

Variable in Set Analysis

darrellbutler — Tue, 15 Mar 2011 16:51:57 GMT

Excellent - Thankyou Miguel.

Why does the quote start before the dollar ?

Many thanks

Variable in Set Analysis

Miguel_Angel_Baeyens — Tue, 15 Mar 2011 17:11:54 GMT

Hi,

In Set Analysis in particular there are three possible quotings

1.- When the value expected is numeric, no quoting: Sum({< ID = {1000} >} Amount)

2.- When the value is a string (your case), single quoting: Sum({< Name = {'John'} >} Amount)

3.- When the value requires search, double quoting: Sum({< CustomerID = {"AA*"} >} Amount)

In your case, the field returns a literal (string), that it's not quoted within the variable. That's why you need single quoting before and after writing the variable.

Hope that makes sense.