topic Re: Count Function in QlikView

Count Function

Wed, 22 Apr 2015 21:11:07 GMT

I'm attempting to build an expression using the count function that shows me the count for a dimension based on a date. The current expression I have is as follows:

=Count({$<[POS Date]={'10/31/2014'}>}[Instrument Type])

Any idea why it does not work?

Re: Count Function

jaimeaguilar — Wed, 22 Apr 2015 21:12:44 GMT

Hi,

it could be easier to help you, if you can upload a little sample of your data,

regards

Re: Count Function

Wed, 22 Apr 2015 21:26:19 GMT

Essentially what I'm trying to do is

Select Count(Instrument Type)

from <table)

where POS Date = '10/31/2014'

group by Instrument Type

Does this make sense?

Re: Count Function

ziadm — Wed, 22 Apr 2015 22:11:58 GMT

Hi =count({$} [Instrument Type]) Your set Analysis seems to be ok in terms of syntax and should not have a syntax error Do you have a zero value return. if so this means the condition found no data and maybe you should remove the $ for having the current selection Try this =count({} [Instrument Type]) Thanks Ziad

Re: Count Function

ziadm — Wed, 22 Apr 2015 22:15:16 GMT

Sorry Try this count({} [Instrument Type])

Re: Count Function

ziadm — Wed, 22 Apr 2015 22:16:50 GMT

=count({<[POS Date] ={'10/31/2014'}>} [Instrument Type])

I am not sure why the editor removes the single qoute

but anyway try removing the $ sign of the current selection

Re: Count Function

ToniKautto — Wed, 22 Apr 2015 22:54:15 GMT

The outcome of an expression will always depend on the dimension(s) that you use. The dimension and expression will then depend on your data model, so with all the details it is hard to advise on how you can address the issue.

If you can provide a simple sample QVW file with a description of you expected result, it will be possible to determine why you get your current results and also advise on changes to get your expected result

Re: Count Function

maxgro — Wed, 22 Apr 2015 23:07:11 GMT

PFA

Re: Count Function

anbu1984 — Thu, 23 Apr 2015 05:06:14 GMT

Is format of [POS Date] is 'MM/DD/YYYY' ?

If [POS Date] is date then try this

=Count({$<[POS Date]={"$(=Date#('10/31/2014','MM/DD/YYYY'))}>}[Instrument Type])

Re: Count Function

Thu, 23 Apr 2015 15:13:51 GMT

Anbu,

I tried that syntax and am seeing a syntax error with the parens. See screen grab below:

From what I can see the Parenthesis line up.

Re: Count Function

ToniKautto — Thu, 23 Apr 2015 22:18:21 GMT

The set expression search string needs to be closed with a double-quote.

=Count({$<[POS Date]={"$(=Date#('10/31/2014','MM/DD/YYYY'))"}>}[Instrument Type])

However, there is no point in making a static calculation like =Date#('10/31/2014','MM/DD/YYYY') in the set expression. This will only result in a text looking like 10/31/2014, meaning that the calculation just makes the expression look more complex and wastes time for one calculation. Just write the date direct in the expression, and this is equal to the expression above.

=Count({$<[POS Date]={"10/31/2014"}>}[Instrument Type])

Re: Count Function

Mon, 27 Apr 2015 13:42:42 GMT

Toni,

Thanks for the response, however I am still unable to get the expression to function. When I use just the following:

=Count([Instrument Type])

I get the following results:

When I try your expression, no results are returned when I know there should be.

Re: Count Function

anbu1984 — Mon, 27 Apr 2015 14:48:28 GMT

Can you post sample app

Re: Count Function

Mon, 27 Apr 2015 14:51:42 GMT

It turns out the way I loaded the data was in a format that was inconsistent. I changed the format and reloaded and this worked.

Thanks.

Re: Count Function

Peter_Cammaert — Mon, 27 Apr 2015 14:56:15 GMT

Probably the [POS Date] field doesn't contain anything recognizable by QlikView as a date. Can you post a screenshot of a [POS Date] listbox? Or a small test document?

In the meantime and on condition that you're not using a PE Desktop, check out Massimo's example higher up.

Peter