Count with a specific criteria

[#1]

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?

[#2]

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.

[anbu1984]

Are you not getting correct result using this expr?

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

[#4]

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.

[michael_anthony · Accepted solution]

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. 

[#6]

Yes. Thanks Michael. That works perfect.