topic Formula in set expression in QlikView

Formula in set expression

guyvermeiren — Tue, 31 May 2011 11:49:42 GMT

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

Formula in set expression

erichshiino — Tue, 31 May 2011 12:02:33 GMT

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)

=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

Formula in set expression

Tue, 31 May 2011 12:05:29 GMT

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!