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!

Count by Year where Year is not a field 1

Status
Not open for further replies.

LizSara

Technical User
Oct 1, 2007
1,503
GB
I'm having brain freeze on the best way to do this.

I have a crosstab report listing routes as it's rows and giving a count of people on those routes as it's columns.

Fairly simple yes?

What i need it to do is count how many of those people were on that route per year for the last 5 years.

The people need to be counted for all the years they are on the route, not just the year they start.

Year isn't a field that i can use (i will have to create a formula) but i do have route start and end date

what i need the report to look like is

03/04 04/05 05/06 06/07 07/08 08/09
route1 xxx xxx xxx xxx xxx xxx
route2 xxx xxx xxx xxx xxx xxx

"Whoever battles with monsters had better see that it does not turn him into a monster. And if you gaze long into an abyss, the abyss will gaze back into you." ~ Nietzsche"
 
Looks like your years are not calendar years. You should be able to use a Crosstab with a formula as you column.

If {routeStart} in Date(2003,04,01) to Date(2004,03,31) then "03/04" else
If {routeStart} in Date(2004,04,01) to Date(2005,03,31) then "04/05" else
etc

Ian
 
What data do you have about the travellers?

Do you have a travel date or date range?

Ian
 
Sorry being dim!!

You will need to do a manual cross tab, create 5 formula

@03/04
If {routeStart} >= Date(2003,04,01) and {routeStart} <=Date(2004,03,31) then 1 else 0

@04/05
If {routeStart} >= Date(2004,04,01) and {routeStart} <=Date(2005,03,31) then 1 else 0

Place these in details an suppress section.
Group report by Route and then sum each o your formula.

Ian
 
Now being really dim, second date should of course be end date, thats the trouble with copy and paste.


@03/04
If {routeStart} >= Date(2003,04,01) and {routeend} <=Date(2004,03,31) then 1 else 0

@04/05
If {routeStart} >= Date(2004,04,01) and {routeEnd} <=Date(2005,03,31) then 1 else 0


Ian
 
Its a bad day on the south coast.

Try

@03/04
If {routeStart} >= Date(2003,04,01) or {routeend} >=Date(2004,03,31) then 1 else 0

@04/05
If {routeStart} >= Date(2004,04,01) or {routeEnd} >=Date(2005,03,31) then 1 else 0

Changed and to or and end date now >=


Ian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top