Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

returning the latest (max) value into a crosstab 1

Status
Not open for further replies.

n2nuk

IS-IT--Management
Dec 24, 2002
190
GB
Hi there,

I have a table that records a clients support level, this can change during the course of their treatment between medium low or High

attached is a sample of data

client_id_______priority_level_________priority_startdate________priorty_enddate
1234__________medium_____________01/02/2017____________23/04/2017
1234__________low_________________23/04/2017___________18/08/2017
1234__________medium_____________21/08/2017____________

or

client_id_______priority_level_________priority_startdate_______priorty_enddate
4321__________high_______________18/11/2017____________13/02/2018
4321__________medium____________14/02/2018____________16/08/2018
4321__________low________________16/08/2018____________21/10/2018


What I'd like to do is produce a summary report that shows the number of clients by priority level, any ideas how i can do this?









 
I would think you would just need three running totals that each would evaluate on a given priority level.
 
Thanks for replying,

not sure how i would get that to work?!

 
For the running total, do a distinctcount of client ID, evaluate based on a formula, e.g., for the “Low” priority:

{table.startdate}=Maximum({table.startdate},{table.clientID}) and
{table.priority}=“Low”

Reset never.

Repeat for each priority level, just changing the priority type. Place the running totals in the report footer. This assumes you have a group on clientID.

-LB
 
I see in the title that you want this in a crosstab. I do not work with crosstabs very much (it has been years since I needed to). So lbass should be able to assist you better than myself.
 
lbass solution did the trick, many thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top