topic Count with a specific criteria in QlikView

Count with a specific criteria

Mon, 17 Nov 2014 06:55:18 GMT

Hi,

I am building a report for our Consultations department.

Currently all the "Details" of clients who visit the office, is captured on an excel spread sheet.

This list goes back a few years.

I am trying to count the number of times that a client has visit our offices. That is straight forward. However, when I Count, and start using the list boxes to select i.e yesterdays date, it only indicates 1( as client only visited once that day).

Is it possible to bring up the names of clients who visited yesterday, but show me the combined total times client has visit, and not 1?

Re: Count with a specific criteria

sushil353 — Mon, 17 Nov 2014 06:58:17 GMT

Hi,

you can modify your expression like this:

say the listbox/field name containing time i.e Yesterday.. is TimeDim

then in your expression = Count({<TimeDim=>}Visit_id)

or Count({1}Visit_id)

HTH

Sushil

Re: Count with a specific criteria

anbu1984 — Mon, 17 Nov 2014 06:58:35 GMT

Can you provide sample app

Re: Count with a specific criteria

ashfaq_haseeb — Mon, 17 Nov 2014 06:58:59 GMT

Hi,

Please upload sample data and expected output.

Regards

ASHFAQ

Re: Count with a specific criteria

Mon, 17 Nov 2014 07:05:06 GMT

Here is the sample data.

Re: Count with a specific criteria

Mon, 17 Nov 2014 07:12:37 GMT

Hi Sushil,

This does work, but how do I get it to now change and only show me the data, coinciding, to those who visit the office yesterday?

Re: Count with a specific criteria

anbu1984 — Mon, 17 Nov 2014 07:13:02 GMT

Not able to open your QVW file

Re: Count with a specific criteria

Mon, 17 Nov 2014 07:15:05 GMT

What do I need to do in order for you to be able to open it?

Re: Count with a specific criteria

ashfaq_haseeb — Mon, 17 Nov 2014 07:17:19 GMT

HI,

Your qvw cannot be opened

you need to attach it again

Regards

ASHFAQ

Re: Count with a specific criteria

anbu1984 — Mon, 17 Nov 2014 07:17:34 GMT

Can you re-upload QVW

Re: Re: Count with a specific criteria

Mon, 17 Nov 2014 07:33:34 GMT

Hope this works. Could it be due to my link to a sql database?

If it does open, look at the "Client details" sheet. the "Total visits to office" chart, should now only give me the visits as per clients on left chart.

Re: Count with a specific criteria

anbu1984 — Mon, 17 Nov 2014 09:24:02 GMT

Are you not getting correct result using this expr?

count({1}left(AccountNumber,6))

Re: Count with a specific criteria

Mon, 17 Nov 2014 09:42:34 GMT

Hi Anbu,

Yes I am getting the correct answer.

But I would like it to now dynamically change to only show the stats, for those few clients who was in office on selected day.

Re: Count with a specific criteria

michael_anthony — Mon, 17 Nov 2014 09:50:54 GMT

What about If (count (visit_id)> 0, count({1} visit_id). It should ony calc the total visits if had at least one in the selected date.

Or could do the test as a calculated dimension.

Re: Count with a specific criteria

Mon, 17 Nov 2014 10:00:37 GMT

Yes. Thanks Michael. That works perfect.