topic Set Analysis Expression Help in QlikView

Set Analysis Expression Help

Wed, 23 May 2012 18:44:10 GMT

Month_Year is in the format 'MMM-YYYY'

vYearStart = Date(YearStart(Today()),'MMM-YYYY') which results in Jan-2012

vCurrMonth = =Date(QuarterEnd(Today()),'MMM-YYYY') which results in Jun-2012

What is the right syntax to get sum of values between Jan and Jun 2012

I am writing the expression

Sum({<Month_Year={">=(vYearStart)<=(vCurrMonth)"}>} Value)

Please enlighten me if i am missing something.

Set Analysis Expression Help

Anonymous — Wed, 23 May 2012 18:52:51 GMT

Try this syntax to start:
Sum({<Month_Year={">=$(vYearStart)<=$(vCurrMonth)"}>} Value)

I am assuming you are setting the variables with the expression, and not a LET statement in the script. But either way, the above should work.
-Phil

Set Analysis Expression Help

Wed, 23 May 2012 19:06:56 GMT

I am setting my variables in Variable Overview.

I have tried the above expression.

The result is 0.

What could be the issue here?

Set Analysis Expression Help

swuehl — Wed, 23 May 2012 20:31:54 GMT

How have you created Month_Year?

Using the textual representation of dates or time periods like above often show issues, at least it seems to be not easy to always match the required format. Thus I would recommend using a date type field in your set expression and then use variables or dollar sign expansions that evaluate to numericals, trying to prevent textual date format issues.

Set Analysis Expression Help

erichshiino — Thu, 24 May 2012 02:04:42 GMT

I agree with the problems with the textual represention but I recommend you convert your fields and variables to an integer representation.

Thus,

Your Month_Year could be used to create another field: nMonthYear

Load...

num( Monht_Year) as nMonthYear

The variables will become:

vYearStart = num(YearStart(Today()) )

vCurrMonth = num(QuarterEnd(Today()) )

Set analysis will be: Sum({<nMonthYear={">=$(vYearStart)<=$(vCurrMonth)"}>} Value)

Hope this helps,

Erich

Set Analysis Expression Help

Fri, 25 May 2012 21:03:15 GMT

I would try putting your search criteria in a text box to make sure it is evaluating correclty:

">=(vYearStart)<=(vCurrMonth)"

And as Phil Bishop stated, you need to add dollar signs ($) in front of your variable parenthesis (i.e. $(vYearStart), $(vCurrentMonth))

Set Analysis Expression Help

vgutkovsky — Fri, 25 May 2012 21:25:07 GMT

Make sure both variables start with an '=' in the Variable Overview. Then Phil's expression should work. If it doesn't, try this one:

Sum({<Month_Year={">='$(vYearStart)'<='$(vCurrMonth)'"}>} Value)

(single quotes around the dollar-sign expansion sometimes helps)

Regards,

Vlad