comparing field data 1

[orb353]

I have created a report pulling from a dataset.The report is based on transactions coming through the dataset from a fox pro database. These are some of the fields i am showing in my report.

Transactions Clientid headerid appttype
10028 5001 1007 2
10029 5001 1007 2
10030 5001 1007 2
10031 4772 1008 1
10032 4772 1008 1
10033 5525 1009 -2
10034 5525 1009 -2
10035 6005 1010 3
10036 6005 1010 3
10037 4228 1011 2
10038 4228 1011 2
10039 4228 1011 2

notice each clientid has several tranactions attached

appttypes
2 = repeat clients
3= new client
1= non request
-2= new client

I have counted all clients within the time frame i specified with a distinctcount for a total client count but i want to be able to count how many of these clients were reapeat or new. I have not been able to count how many repeat clients , new clients , etc.
because there is miltiple listings for each client- because most clients get more than one service.
how can i report how many of the client were repeat, new etc.
I would be grateful for help
thanks
orb353

 

[Madawc]

In the examples you show, each record for a client has the same 'appttype'. If it's always like that, then
a) Group by client
b) Do running totals that do a distinct count of the client. Each will test for a particular type.


Madawc Williams (East Anglia, UK)

 

[orb353]

that does not really give me the info i need.
The nearest i come to getting the right info was creating the formula
if{transhead.iapptype}=2 then 1 else 0

then i create a summary-count of that formula.
this returns the total number of transactions that =2, but but each client may have several transaction. So i am getting the total transaction that =2, not the clients that =2. I am trying to classify it by clients .to see how many clients =2 , how many clients =3 etc.

thanks

 

[lbass]

You do need to use running totals. Using the expert, create {#new} where you select distinctcount of {table.clientID}, evaluate using a formula:

{table.appttype} in [-2,3]

Reset never.

For {#repeat} you would change the evaluation formula to:

{table.appttype} = 2

Etc.

-LB

 

[orb353]

I tried it and the repeat returns a value of 1 every time .
new returns 0 every time.

It should work but i cannot get it to.Any suggestions as to what I may be doing wrong.
the directions LB gave me seem to me to say in english

running total(distinctcount) of {table.clientid) where {table.appttype}=2

any suggestions

orb353

 

[lbass]

The running totals need to be placed in the report footer. Are you doing that? Can you confirm that you used the running total expert (insert->(field object)->running total)?

-LB

 

[orb353]

wow it works when I drop it in the footer
is there any way to show it that total anywhere else in the report?
THANKS
THAT is Awesome
Thank you very much for your help
orb353

 

[lbass]

If you wanted to show the grand total (based on a running total) anywhere else in the report, you would need to save the report as a subreport and insert it into the original report, suppressing all subreport sections except the report footer.

-LB