I have a crosstab set up that shows the number of cases done by surgeon per month from July 2007 to September 2008.
My question is I want to sum by surgeon (summing across rows) for July 07 to June 08 and then again from June 08 to Sep 08 not just across the total months listed. Is there a way to do this? And can I insert a column just between June and July 08 for this sum (July 07-June08)
I know that I could run the data for July-June 08 and then insert another table for July-Sept 08, but then the surgeon data changes.
Even if a surgeon did no cases in a one of the dates ranges that I want to sum, I'd still like to keep that surgeon on the list to keep it consistent.
I hope this all makes sense.
Any help is appreciated.
Harry van der Kolk replied
Looks like you have a fiscal year running from July to June next year.
If in your universe (assuming you are using one) you have available an object for fiscal year then please use that as one of the objects in your 'Result Objects'. You can then use that object in your crosstab and put a break on it.
If you don't have such an object available it's a bit more work. You then need to create a variable called 'fiscal year' (or anything else if you want).
The formula for the variable will then be:
=If <object year> = 2007 or (<object year> = 2008 and <object month> <=6) then 2007 else 2008
You can then use this variable in your crosstab and put a break on it.
After you created the break you can add a sum to the break footer.