Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 662
  • Last Modified:

How to count number of occurrences of data subject to a condition

I have a table containing sales leads. Each lead has a type, salesperson and a date created. I have a report showing total number of leads of type A against each salesperson - this is done by using the select expert to filter the leads to include only type A.

My problem is I want a second column to show the number of leads of type A which have a date created value that falls within a date range that I am entering as a parameter field. This is effectively a subset of the first column data. Is there an easy way to do this?
0
rjm2803
Asked:
rjm2803
1 Solution
 
petr_hlucinCommented:
The attached code snippet should help.
select count(*) as sales_count, salesperson
from sales
group by salesperson;

Open in new window

0
 
peter57rCommented:
It sounds like you could use a running totals field for this.

Count any field , reset on change of group and for the evaluate when use a formula that says something like:

{table.datefield} in {?dateparam}
0
 
mlmccCommented:
Agree with the running total idea.

You could also use a simple formula that tests for meeting the conditions

If ({DateFIeld} in {?DateParam}) then
    1
else
    0

You can then use the summary functions on the formula to get the count.

mlmcc
0
 
rjm2803Author Commented:
Thanks for that  - simple and works well
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now