Expression with Dates and Texts criteria

[rafaeloneil]

Guys, good afternoon,

I am trying to create an expression that perform the counting of a field by applying multiple criteria in other fields. The Criteria Are:

GR_MERCADO_2={‘Bradesco’}

UF_LICENCIAMENTO={‘SP’, ‘PE’, ‘SC’}

NOT IsNull(ANO_EMISSAO_DOC)

Date(MakeDate(ANO, MES, DIA), 'DD/MM/YYYY') >= 01/05/2018 **

 My Expression is not working: 

COUNT({$<GR_MERCADO_2={'Bradesco'}, UF_LICENCIAMENTO={'SP', 'PE', 'SC'}, NOT IsNull(ANO_EMISSAO_DOC), Date(MakeDate(ANO, MES, DIA), 'DD/MM/YYYY') >= 01/05/2018>}[CHASSI/SEQ])

** I need to mount the date because the base does not have a unified date field

Can you give me a direction?

[Lisa_P]

Inside the Set Analysis { }, you can define fields, but not the other criteria. Could you find another criteria for the ANO_EMISSAO_DOC field (>0)?

[jonathandienst · Accepted solution]

The LHS of a set expression clause must be a physical field name, so your requirement cannot be implemented purely in a set expression. You could do this:

COUNT({$<
	GR_MERCADO_2={'Bradesco'}, 
	UF_LICENCIAMENTO={'SP', 'PE', 'SC'}, 
	ANO_EMISSAO_DOC = {"*"}
>} If(MakeDate(ANO, MES, DIA) >= Date#('01/05/2018'), [CHASSI/SEQ]))

Or define a new field with the MakeDate() expression in your load script, and then add the new field criterion to the set expression.  Something like this:

DateNew = {">=$(=Date#('01/05/2018'))"}

(the exact syntax depends on your default date format)