Count by Year where Year is not a field 1

[LizSara]

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"

 

[IanWaterman]

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

 

[LizSara]

I tried that, the problem was it only put the people in the year that the route started not that year and every subsequent year until their route end date

"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"

 

[IanWaterman]

What data do you have about the travellers?

Do you have a travel date or date range?

Ian

 

[LizSara]

i have the start and end date of when they are on the route

"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"

 

[IanWaterman]

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

 

[IanWaterman]

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

 

[LizSara]

That formula doesn't work. If someone starts on a route on 01/09/2004 and ends on 31/08/2008 they need to be counted in each year, and that doesn't do that.

"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"

 

[IanWaterman]

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

 

[LizSara]

That looks spot on to me...thank you so much, lots of stars for you

"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"