Menu
This would seem to be a simple task but I'm having difficulty executing it as well as finding examples of how it is done in R.
I have a data frame with about 75K records. One field has dates and it goes back about 11 years. I've used the following code to strip off the hours minutes seconds so I just have year-month-day
dat$date=round.POSIXt(dat$date,units="day")
I am now trying to create a new field "FiscalYear" based on dates such that if the date is between 2008-07-01 and 2009-06-30 it gets assigned FY09...then between 2009-07-01 and 2010-06-30 it gets assigned to FY10, etc...
The method I've been trying is with ifelse using >= && <= to set the date ranges but this isn't working. Any thoughts?
211
Select any cell in the Date column in the Pivot Table. Go to Pivot Table Tools –> Analyze –> Group –> Group Selection. In the Grouping dialogue box, select Quarters as well as Years. You can select more than one option by simply clicking on it.
Right-Click on any cell within the Dates column and select Group from the fly-out list. Then select Month in the dialog box. Using the Starting at: and Ending at: fields, you can even specify the range of dates that you want to group if you don't want to group the entire list.
If you specify the date field as row label in a pivot table, you can easily group dates by Week, month, quarter, etc. But, if you specify a number filed as row label, could you group by specified range? Of course yes! This article will guide you to group by the range in an Excel pivot table.
If you have Kutools for Excel, with its PivotTable Special Time Grouping feature, you can quickly group date by fiscal year, half year, week number, day of week, half an hour or specific minutes as you need.
(In Excel 2016 and later versions, it will automatically group the Date into Years and Quarters), see screenshot: 4. Then go to the pivot table, right click anywhere under the Row Labels head, and select Group.
Now go to the PivotTable Fields pane, please drag and drop Score field to the Rows section, and drag and drop Name field to the Values section. 4. Go to the pivot table, right click any score in the Row Labels column, and select Group from the context menu.
Basically, what Ben said. Here's an example of what that'd look like in practice. (I've used a Date class object, since it sounds like you don't need the hour/minute/second info.)
date <- seq(from = as.Date("2010/5/30"), by="week", length=10) ## Example data
cuts <- seq(from = as.Date("2000/7/1"), by="year", length=13)
labs <- paste0("FY", 1:12)
cut(date, breaks = cuts, labels = labs)
# [1] FY10 FY10 FY10 FY10 FY10 FY11 FY11 FY11 FY11 FY11
# Levels: FY1 FY2 FY3 FY4 FY5 FY6 FY7 FY8 FY9 FY10 FY11 FY12
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
