Set Analysis: Count with Left

[#1]

Hi,

I'm trying to count the numbers of Matters opened in a given time period, and the expression I currently have is:

Count({<left(MATTER.OPEN_DATE, 4)={$(YTD)}>} (MATTER.CLNT_MATT_CODE))

Where MATTER.OPEN_DATE is of the format '1901-01-01 00:00:00.000'

YTD is a variable, an example of which is '2014'

and MATTER.CLNT_MATT_CODE is a unique identifier for a Matter

The QV expression editor indicates that the expression is OK, but the above expression returns no data (I would expect a count of thousands).

I'd appreciate any suggestions.

Thanks, Gavin

[Nicole-Smith · Accepted solution]

You can't have a calculation to the left of the equal sign in set analysis.  Try this:

Count({<MATTER.OPEN_DATE={'=left(MATTER.OPEN_DATE, 4)=$(YTD)'}>} MATTER.CLNT_MATT_CODE)

If that doesn't work, you need to create a new field left(MATTER.OPEN_DATE, 4) as NewField in your script and use that in the set analysis instead:

Count({<NewField={$(YTD)}>} MATTER.CLNT_MATT_CODE)

[#3]

Thank you Nicole. I think that would have worked perfectly, however for some reason the 'Left' function is not behaving as I would have expected. Instead of returning, for example, '2014', it is instead returning, for example '4165'.

I realise that this is a separate question so will mark this as correct.

[Nicole-Smith]

Instead of left(MATTER.OPEN_DATE, 4), go with year(MATTER.OPEN_DATE).

[#5]

Thanks again Nicole.