topic Re: Help with Set Expression in QlikView

Help with Set Expression

Wed, 20 Aug 2014 19:25:44 GMT

I have a field called StatusMonth. Values are strings of the form 'YYYY-MM mmm' (e.g. '2014-01 Jan').

I also have a variable that I set called vCurrent13Month and it also is a string of the form 'YYYY-MM mmm'.

I want to count the number of units where the StatusMonth is greater than or equal to vCurrent13Month but
am having difficulty with the Set expression.

Here's what I've got:

=Count({1 <StatusMonth={">=$(vCurrent13Month)"}> DISTINCT UnitName}

And it's just not working. I've tried all kinds of variations and no luck.

Can anyone tell me why the above doesn't work?

Thanks,
rpb

Re: Help with Set Expression

Wed, 20 Aug 2014 19:39:30 GMT

DOH! I've been staring at this for over an hour and finally see it. I really need to get some better glasses. I ended the Count() function using a squiggly bracket instead of a paren. So it now looks like

Count({1 <StatusMonth={">=$(vCurrent13Month)"}>} DISTINCT UnitName)

But still does not seem to be returning the right answer

Re: Help with Set Expression

Wed, 20 Aug 2014 19:43:58 GMT

Hi,

Try this:

Count({1<StatusMonth={">='$(vCurrent13Month)'"}>} DISTINCT UnitName) // LOOK -->'$(vCurrent13Month)' to read values as text.

Best regards.

Re: Help with Set Expression

Wed, 20 Aug 2014 19:44:02 GMT

I'd venture to say that it's because your StatusMonth and vCurrent13Month are being interpreted as a literal string and not a number.

The set modifier (<StatusMonth={">=$(vCurrent13Month)"}>) can not find greater than or equal to a text string.

Look in to using MakeDate or Date# or Dual.

To optimize this calculation I'd use a flag in your load script:

1 as UnitNameFlag

and your expression would be similar to: =sum({<StatusMonth={">=$(vCurrent13Month)"}>}UnitNameFlag)

If you'd like to upload your qvw I can take a look at it.

Re: Help with Set Expression

Wed, 20 Aug 2014 19:54:47 GMT

I'd venture to say that it's because your StatusMonth and vCurrent13Month are

being interpreted as a literal string and not a number

Which is exactly what I expected.

The set modifier (<StatusMonth={">=$(vCurrent13Month)"}>) can not find

greater than or equal to a text string.

Which surprises me. Are you saying there is no way to compare if one string is "greater than" another?

I would expect that "2013-08 Aug" is Less Than "2014-07 Jul".

Is that what's wrong here?

Re: Help with Set Expression

Wed, 20 Aug 2014 19:55:33 GMT

Oswaldo,

Tried adding the quotes. No change.

Appreciate it, though!

rpb

Re: Help with Set Expression

jerem1234 — Wed, 20 Aug 2014 20:18:44 GMT

I would change the field to be in a date format in script, not text, use something like:

Date#(DATEFIELD, 'YYYY-DD MMM') as NEWDATEFIELD

Hope this helps!

Re: Re: Help with Set Expression

Wed, 20 Aug 2014 20:26:44 GMT

Richard,

See attachment

Re: Help with Set Expression

Wed, 20 Aug 2014 20:31:50 GMT

Thank you, Alex. Will go that route.

Re: Help with Set Expression

Wed, 20 Aug 2014 20:47:58 GMT

In your load statement, I would try something like this:

1) Set the vCurrent13Month variable to a date variable:

SET vCurrent13Month = "Date#('2014-01 Jan', 'YYYY-DD MMM')";

2) When loading the StatusMonth field, convert it to a date value:

LOAD

...

Date#(StatusMonth, 'YYYY-DD MMM') as StatusMonth

...

Re: Help with Set Expression

Thu, 21 Aug 2014 13:23:49 GMT

Alex,

Trying to implement this in script and keep getting "Syntax error, missing/misplaced FROM: LOAD"

Am confused. Here is my script:

CONNECT32 TO [MS Access Databases;DBQ=C:\Users\DickByrne\Documents\ITO Customer Service\BU Metrics\Dashboard\GSS Insight\KPI Dashboard\Global KPI Data.accdb];
//-------- Start Multiple Select Statements ------
LOAD
StatusMonth,
MakeDate(Left(StatusMonth, 4), Mid(StatusMonth, 6, 2)) As Period,
PriorMonth,
MakeDate(Left(PriorMonth, 4), Mid(PriorMonth, 6, 2)) As PriorPeriod,
CurrentYear,
PriorYear,
RollAvgStart,
MakeDate(Left(RollAvgStart, 4), Mid(RollAvgStart, 6, 2)) As RollAvgStartPeriod,
RollAvgEnd,
MakeDate(Left(RollAvgEnd, 4), Mid(RollAvgEnd, 6, 2)) As RollAvgEndPeriod,
[13MonthStart],
MakeDate(Left([13MonthStart], 4), Mid([13MonthStart], 6, 2)) As 13MonthStartPeriod;
SQL SELECT *
FROM StatusMonths;

SQL SELECT *
FROM Units;

SQL SELECT *
FROM `KPI_Data`;

SQL SELECT *
FROM `Sev1_Data`;
//-------- End Multiple Select Statements ------

Re: Help with Set Expression

Thu, 21 Aug 2014 13:32:29 GMT

Richard,

Putting brackets around your 13MonthStartPeriod should take care of it;

[13MonthStartPeriod]

The error "Syntax error, missing/misplaced FROM: LOAD" typically means you're missing a comma, semicolon or an error that relates to what you've received (sometimes it's from a space in the field name too). In this instance it's the leading integer.

Re: Help with Set Expression

Thu, 21 Aug 2014 13:55:30 GMT

Many thanks, Alex! That did it.