0

I am trying to find the total revenue, gross, margin, sales, and inventory by week in R. I believe the easiest way to do this is assign a week number to each date so I can group by or filter for the week number. However, there are multiple years and I can only figure out how to do this so it resets at Week 1 at the beginning of the next year. Is there a way to get around this? Should I add a year column for each of the purchases as well?

WeeklyVariableData$New$WeekNum <- strftime(WeeklyVariableData$New$'Contract Date', format = "%V")
WeeklyVariableData$Used$WeekNum <- strftime(WeeklyVariableData$Used$'Contract Date', format = "%V")

WeeklyVariableData$New$Year <- strftime(WeeklyVariableData$New$'Contract Date', format = "%Y")
WeeklyVariableData$Used$Year <- strftime(WeeklyVariableData$Used$'Contract Date', format = "%Y")
talkingtoducks
  • 57
  • 9
  • It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. – MrFlick Nov 23 '22 at 17:37
  • 1
    I don't understand what you mean by *"it resets at Week 1 at the beginning of the next year"*. As MrFlick suggested, please [edit] your question to add: sample data, code you're currently using to try to aggregate by-week, and why it is incorrect. Since "year" appears to be a challenge in the aggregation, please make sure your sample data includes this corner-case. – r2evans Nov 23 '22 at 17:42
  • I am trying to figure out if there is a way that I can share some of the data without sharing personal identifiers. What I mean by resetting is, since there are only 52-53 weeks in a year, at the beginning of the next year , the next year the week number is 1 again instead of continuing the count. – talkingtoducks Nov 23 '22 at 18:36
  • `NCTotRev <- WeeklyVariableData$New %>% group_by(WeekNum, Year) %>% summarize(NCTotRev = sum('Sold Price'))` `dput(NCTotRev[1:10, ])` `dput(WeeklyVariableData$WeeklyData[1:10, 0])` – talkingtoducks Nov 23 '22 at 18:42

1 Answers1

0
WeeklyVariableData$New$WeekYear <-
  strftime(WeeklyVariableData$New$'Contract Date', format = "%V/%Y")

WeeklyVariableData$Used$WeekYear <-
  strftime(WeeklyVariableData$Used$'Contract Date', format = "%V/%Y")

This formats the date to include both the week and the year. While its not the continuing the count at the beginning of the next year, it makes things a lot easier.

cgvoller
  • 873
  • 1
  • 14
talkingtoducks
  • 57
  • 9