topic Expression Help in QlikView

Expression Help

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression Help

marcus_malinow — Wed, 27 Sep 2017 14:29:12 GMT

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

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

Re: Expression Help

vishsaggi — Wed, 27 Sep 2017 15:14:49 GMT

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]))

Re: Expression Help

Anonymous — Wed, 27 Sep 2017 15:22:05 GMT

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

Re: Expression Help

Anonymous — Wed, 27 Sep 2017 15:24:33 GMT

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

Re: Expression Help

marcus_malinow — Wed, 27 Sep 2017 15:26:56 GMT

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])

Re: Expression Help

vishsaggi — Wed, 27 Sep 2017 15:27:13 GMT

Can you share that file where you getting this error?

Are you using the expression i sent you earlier msg?

Re: Expression Help

marcus_malinow — Wed, 27 Sep 2017 15:28:15 GMT

posted in error

Re: Expression Help

Anonymous — Wed, 27 Sep 2017 15:35:46 GMT

Here you go.

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

Thanks

Gareth

Re: Expression Help

vishsaggi — Wed, 27 Sep 2017 15:40:56 GMT

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)

Re: Expression Help

Anonymous — Wed, 27 Sep 2017 15:53:05 GMT

Thank you,

I'm getting closer!!

The expression is now returning 24,594 instead of 23,157 for Jan 2015.

Thanks,

Gareth

Re: Expression Help

vishsaggi — Wed, 27 Sep 2017 15:58:42 GMT

Why it should be 23,157? Can you explain a little here please?

Re: Expression Help

Anonymous — Thu, 28 Sep 2017 07:47:17 GMT

Hi,

If you export the straight table and filter the date to just be Jan-2015, there are 23,157 records.

Thanks,

Gareth

Re: Expression Help

marcus_malinow — Thu, 28 Sep 2017 07:56:45 GMT

Hi Gareth,

it might be useful here if you go into a bit more detail.

What are you trying to count here? Suppliers? Invoices?

What is the reason for your set analysis?

What is the relationship between Invoice Date and your Year and Month fields?

Marcus

Re: Expression Help

Anonymous — Thu, 28 Sep 2017 08:21:36 GMT

Hi Marcus,

Further information as requested:

What are you trying to count here? Suppliers? Invoices? For now, the number of invoices submitted within the selected range.

What is the reason for your set analysis? Advised this was the best way to do it, and if I decide to count the number of suppliers that have submitted an invoice in the set selection then it will be a relatively easy change in the expression.

What is the relationship between Invoice Date and your Year and Month fields? The Year and Month fields are derived from the Invoice Date field at the time of loading the data into the app.

Hope this helps,

Gareth

Re: Expression Help

marcus_malinow — Thu, 28 Sep 2017 08:26:44 GMT

Hi Gareth,

What is the relationship between Invoice Date and your Year and Month fields? The Year and Month fields are derived from the Invoice Date field at the time of loading the data into the app.

In which case there may be an issue. Create a table box with Invoice Date, Year and Month.

A few example rows - Invoice Date here seems to bear no relation to Year and Month

Invoice Date	Year	Month
01/06/2013	2015	Jan
08/10/2013	2015	Jan
23/10/2013	2015	Jan
07/11/2013	2015	Jan
13/12/2013	2015	Jan
22/12/2013	2015	Jan
23/12/2013	2015	Jan
24/12/2013	2015	Jan
01/01/2014	2015	Jan

Marcus

Re: Expression Help

dinuwanbr — Thu, 28 Sep 2017 08:58:36 GMT

Hi,

PFA

Rgds,

Tharindu

Re: Expression Help

Anonymous — Thu, 28 Sep 2017 09:14:42 GMT

Thanks Marcus,

Yup, I think there was indeed a problem. Tried it all with different sample data and it works fine now.

Many thanks for your help,

Gareth