Formula in set expression

[guyvermeiren]

Hello,

I'm trying to filter a number of records by set expression. Here's my formula:

=count({$<[Opportunity Positions Sales Team.Primary]= {'Y'},[Opportunity.Status]={'Pending'},

Date(left(Opportunity.Created,10),'DD/MM/YYYY'])>{'01/06/2010'}>}Opportunity.Number)

It's on the part Date(left(Opportunity.Created,10),'DD/MM/YYYY'])>{'01/06/2010''} that I get an error

Anyone any idea what the problem is ?? Probably wrong syntax

Thanks already

Kind regards,

Guy

[erichshiino]

You can not make this kind of transformation in a field on set analysis (at least , not in the field list)

I suggest you create a field with that expression on your script, and then, call it on set analysis.

(There is an extra ] there too and it probably should be Date# to transform a string to date)

So, in the middle of a load in the script, include this:

Load [.... your regular load script ] ,

Date#(left(Opportunity.Created,10),'DD/MM/YYYY') as OppportunityDate,

num(Date#(left(Opportunity.Created,10),'DD/MM/YYYY'))as nOppportunityDate, //this one may be a better approach

from (etc...)

Your set analysis will be like this:

=count({$<[Opportunity Positions Sales Team.Primary]= {'Y'},[Opportunity.Status]={'Pending'},

OpportunityDate ={'>01/06/2010'}>}Opportunity.Number)

or

=count({$<[Opportunity Positions Sales Team.Primary]= {'Y'},[Opportunity.Status]={'Pending'},

nOpportunityDate ={'>num(today()-90)'}>}Opportunity.Number)

The second format (date as number) may work better with setanalysis. You just need to convert your date to number using num() before using it.

Hope it helps,

Erich

[#3 · Accepted solution]

You should try to explain what you're trying to accomplish .

The syntax is wrong and I can't get what you wanna do...

The base syntax is Count( { $<field1 = Set1, ..., fieldN = SetN> } Opportunity.Number)

What you probably want is this:

=count(

     {$<

          [Opportunity Positions Sales Team.Primary]= {'Y'},

          [Opportunity.Status]={'Pending'},

          Opportunity.Created = {'>=01/06/2010 00:00:00'}

     }>}

     Opportunity.Number

)

You specify for the "Opportunity.Created" field a set defined by a condition.

Note that you should adjust the syntax of the date according to your load script (first page in the load script).

If you want to try it to be sure how it works, create a list box for the field, click on the search button (magnifying glass) in the list box and write the set expression (>=01/06/2010 00:00:00). If it works, you're done, if not, try varying the datetime string format.

If it works in listbox search, it works in set analysis.

Hope this helps!