Sure, let me give this a try. Here's a tibble that illustrates what I want the output to look like:
tibble::tribble(
~Account, ~Type, ~MonthlyRate, ~ContractStart, ~ContractEnd, ~6/30/2018, ~7/31/2018, ~8/31/2018, ~9/30/2018, ~10/31/2018,
"A", "W", "$50", "1/1/2018", "12/31/2018", "$50.00", "$50.00", "$50.00", "$50.00", "$50.00",
"A", "X", "$75", "8/1/2018", "3/14/2019", "$0.00", "$0.00", "$75.00", "$75.00", "$75.00",
"B", "W", "$60", "2/28/2018", "9/28/2018", "$60.00", "$60.00", "$60.00", "$56.00", "$0.00",
"B", "X", "$90", "5/12/2018", "8/11/2018", "$90.00", "$90.00", "$31.94", "$0.00", "$0.00",
"B", "Y", "$45", "2/28/2018", "9/30/2018", "$45.00", "$45.00", "$45.00", "$45.00", "$0.00",
"C", "Y", "$50", "7/31/2018", "4/30/2019", "$0.00", "$1.61", "$50.00", "$50.00", "$50.00",
"D", "W", "$65", "1/1/2019", "3/31/2019", "$0.00", "$0.00", "$0.00", "$0.00", "$0.00",
"D", "Y", "$50", "9/1/2018", "5/31/2019", "$0.00", "$0.00", "$0.00", "$50.00", "$50.00",
"D", "Z", "$110", "8/22/2018", "10/15/2018", "$0.00", "$0.00", "$31.94", "$110.00", "$53.23",
"E", "Z", "$100", "10/1/2018", "9/30/2019", "$0.00", "$0.00", "$0.00", "$0.00", "$100.00"
)
I would then execute the following:
excluded_cols <- c("MonthlyRate", "ContractStart", "ContractEnd")
long_bookings <- bookings %>%
select(-excluded_cols) %>%
gather(key = "reporting_date", value = "revenues", -Account, -Type)
This gives me the following:
> long_bookings
# A tibble: 50 x 4
Account Type reporting_date revenues
<chr> <chr> <chr> <dbl>
1 A W 6/30/2018 50
2 A X 6/30/2018 0
3 B W 6/30/2018 60
4 B X 6/30/2018 90
5 B Y 6/30/2018 45
6 C Y 6/30/2018 0
7 D W 6/30/2018 0
8 D Y 6/30/2018 0
9 D Z 6/30/2018 0
10 E Z 6/30/2018 0
# ... with 40 more rows
Finally, I'd group and summarize:
long_bookings %>%
group_by(reporting_date, Type) %>%
summarize(MonthRev = sum(revenues))
Which gives me:
# A tibble: 20 x 3
# Groups: reporting_date [?]
reporting_date Type MonthRev
<chr> <chr> <dbl>
1 10/31/2018 W 50
2 10/31/2018 X 75
3 10/31/2018 Y 100
4 10/31/2018 Z 153.
5 6/30/2018 W 110
6 6/30/2018 X 90
7 6/30/2018 Y 45
8 6/30/2018 Z 0
9 7/31/2018 W 110
10 7/31/2018 X 90
11 7/31/2018 Y 46.6
12 7/31/2018 Z 0
13 8/31/2018 W 110
14 8/31/2018 X 107.
15 8/31/2018 Y 95
16 8/31/2018 Z 31.9
17 9/30/2018 W 106
18 9/30/2018 X 75
19 9/30/2018 Y 145
20 9/30/2018 Z 110