topic Re: Set Analysis: Count with Left in QlikView

Set Analysis: Count with Left

Mon, 20 Jan 2014 15:00:24 GMT

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

Re: Set Analysis: Count with Left

Nicole-Smith — Mon, 20 Jan 2014 15:03:53 GMT

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)

Re: Set Analysis: Count with Left

Mon, 20 Jan 2014 15:55:54 GMT

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.

Re: Set Analysis: Count with Left

Nicole-Smith — Mon, 20 Jan 2014 16:02:10 GMT

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

Re: Set Analysis: Count with Left

Mon, 20 Jan 2014 16:26:41 GMT

Thanks again Nicole.