Expression Help

[Anonymous]

Hi guys

In my attached dummy file, I am trying to count the number of invoices within a certain period.

However, I am getting the error "Error: Error in expression: '}' expected".

How do I resolve this? Could someone please have a look at the attached and advise?

Many thanks,

Gareth

[marcus_malinow]

Hi Gareth,

try this:

Count({<Year=, Month=, [Invoice Date]={"<=$(=MonthEnd(MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1)))=>$(=MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1))"}>} DISTINCT [Supplier Name])

or this

Count({<Year=, Month=, [Invoice Date]={"<=$(=MonthEnd(MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1)))=>$(=MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1))"}>} [Supplier Name])

A couple of issues:

- your syntax was incorrect for the Invoice Date search string. It should be of the form

[Invoice Date]={">=01/07/2017<=31/07/2017"}

Note the use of double quotes (indicating that this is a search string rather than an exact match). Also note that we include the comparison operators within the searcn string

- within your search string you have to include dollar sign expansion to ensure that this is evaluated prior to the outer expression being evaluated

- given that your year and month fields are decoupled from invoice date, you need to make sure your set analysis specifically ignores them

- Supplier Name was incorrectly written as SUPPLIER NAME in the expression.

Marcus

[vishsaggi]

May be try this?

Create a two variables like

LET vMinDate  = MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1) ;

LET vMaxDate = MonthEnd(MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1));

Then use like

= Count( IF( [Invoice Date] >= '$(vMinDate)'  AND [Invoice Date] <= '$(vMaxDate)',  [Supplier Name]))

[Anonymous]

Hi Vishwarath,

Thanks for your reply.

Unfortunately it's not working for me, I now get an error of "Error: Error in expression: ')' expected".

Thanks,

Gareth

[Anonymous]

Hi Marcus

Thanks for your reply.

I have tried this, and while it gives me a numerical result, it's incorrect. For example, there were 23,157 invoices with a January 2015 invoice date. However, the first expression you provided results in 2,654 and the second expression results in 313,214.

Many thanks,

Gareth

[marcus_malinow]

Ok then,

this returns the correct figure - 140,171

=Count({<Year=, Month=, [Invoice Date]={"<=$(=MonthEnd(MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1)))=>$(=MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1))"}>} DISTINCT [Invoice Number])

[vishsaggi]

Can you share that file where you getting this error?

Are you using the expression i sent you earlier msg?

[marcus_malinow]

posted in error

[Anonymous]

Here you go.

Don't think I've done anything different to what you've suggested.

Thanks

Gareth

[vishsaggi · Accepted solution]

Ok add =

equals to sign in your variables like

= MakeDate(GetFieldSelections(Year),Match(left(GetFieldSelections(Month),3), $(=chr(39) & Replace(MonthNames, ';', chr(39) & ',' & chr(39)) & chr(39))),1)