Set modifier question - filter for Year not working

[#1]

I've spent much time searching for what I think is an easy answer, but I haven't yet found the solution.

I am trying to do the simplest of formulas...I created a variable vOpptyCreatedYr = Year(OpptyCreatedDate).  Now I'm trying to create an expression that isn't working:  COUNT (DISTINCT {vOpptyCreatedYr={2013})>} DART__ID),  and while it says the expression is Ok, it is still not filtering for 2013 - there is an error/red error indicator beneath the open bracket just before the "2".

I think this is simple but I can't find what I'm missing.  Thanks in advance for your help!    -Dan

[Nicole-Smith]

An option without the variable (because you can't use a variable like that in set analysis):

COUNT ({<OpptyCreatedDate={'>=$(=makedate(2013))<=$(=yearend(makedate(2013)))'}>}DISTINCT DART__ID)

Or you need to create a OpptyCreatedYear field in your load script, and then you can:

COUNT ({<OpptyCreatedYear={2013}>}DISTINCT DART__ID)

[preminqlik]

you do one thing create

year field at backend and

in front end use

Count({<Year={'2013'}>} distinct DART__ID)

[rustyfishbones]

Create Year in the Script

Year (OpptyCreatedDate) AS Year

And the use the following expression

Count ({<Year ={'2013'}>} DISTINCT DART_ID)

[Clever_Anjos]

you forgot <>

COUNT (DISTINCT {<vOpptyCreatedYr={2013}>} DART__ID)

[#6]

I like the idea of creating the year variable in the script...I'm just not sure where.  I can't do it in the SQL SELECT statment...so how do I get it in the table?

[Nicole-Smith]

YourTable:

LOAD *,

         Year(OpptyCreatedDate) as OpptyCreatedYear;

SQL SELECT

      whatever your stuff is here

So to summarize, you can do a preload to add the year as a field in addition to all of your other fields.

[preminqlik]

hi try this

COUNT (DISTINCT if($(vOpptyCreatedYr)=2013, DART__ID))

[#9]

Thanks for the replies.  In this case, I had 4 other filters to use in the expression, so I just used an COUNT(IF) statement.