Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Try this:
Count({1<StatusMonth={">='$(vCurrent13Month)'"}>} DISTINCT UnitName) // LOOK -->'$(vCurrent13Month)' to read values as text.
Best regards.
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.
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?
Oswaldo,
Tried adding the quotes. No change.
Appreciate it, though!
rpb
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!
Richard,
See attachment
Thank you, Alex. Will go that route.
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
...