Help with Set Expression

[#1]

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

[#2]

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

[#3]

Hi,

Try this:

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

Best regards.

[#4]

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.

[#5]

               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?

[#6]

Oswaldo,

Tried adding the quotes.  No change.

Appreciate it, though!

rpb

[jerem1234]

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!

[#8 · Accepted solution]

Richard,

See attachment

[#9]

Thank you, Alex.  Will go that route.

[#10]

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

...