Cumulative sum from a month ago until the current day for all the rows

Related searches

I have a data.table with ID, dates and values like the following one:

DT <- setDT(data.frame(ContractID= c(1,1,1,2,2), Date = c("2018-02-01", "2018-02-20", "2018-03-12", "2018-02-01", "2018-02-12"), Value = c(10,20,30,10,20)))

   ContractID       Date Value
1:          1 2018-02-01    10
2:          1 2018-02-20    20
3:          1 2018-03-12    30
4:          2 2018-02-01    10
5:          2 2018-02-12    20

I'd like to get a new column with the total cumulative sum per ID from a month ago until the current day for each row, like in the table below. NB: the third row is the sum of the second and the own third, because 2018-03-12 minus 1 month is greater than 2018-02-01, so we exclude the first row in the cum sum.

   ContractID       Date Value Cum_Sum_1M
1:          1 2018-02-01    10         10
2:          1 2018-02-20    20         30
3:          1 2018-03-12    30         50
4:          2 2018-02-01    10         10
5:          2 2018-02-12    20         30

Is there any way to achieve this using data.table?

Thank you!

Using tidyverse and lubridate, we first convert Date to actual Date object using as.Date, then group_by ContractID and for each Date sum the Value which is between current Date and one month before the current Date.


DT %>%
  mutate(Date = as.Date(Date)) %>%
  group_by(ContractID) %>%
  mutate(Cum_Sum_1M = map_dbl(1:n(), ~ sum(Value[(Date >= (Date[.] - months(1))) &
                                            (Date <= Date[.])], na.rm = TRUE)))

# A tibble: 5 x 4
# Groups:   ContractID [2]
#  ContractID Date       Value Cum_Sum_1M
#       <dbl> <date>     <dbl>      <dbl>
#1          1 2018-02-01    10         10
#2          1 2018-02-20    20         30
#3          1 2018-03-12    30         50
#4          2 2018-02-01    10         10
#5          2 2018-02-12    20         30

Cumulative total – DAX Patterns, Inventory table that shows the stock of each product for every month, We want to create a measure that sums all the sales values up to a accumulates values from the beginning of time up to the current date. For example, in order to compute the running total while keeping the filter on the day of the� The goal is to dynamically sum values through a given number of months, hardcoded as 6 in cell G5. This is done by feeding a reference constructed with the OFFSET function into the sum function. The OFFSET function let's you build a reference using a starting point, a row and column offset, and a height and width.

This is largely a rolling sum question. froll() would likely work but you'd have to complete the dataset first so that you can say how many days to roll backwards.

Here I do a non-equi self join. As data.table wants all fields generated before the join, I have to add a column Dates_Lower = Dates-30 so that I can complete the non-equi conditions. My chain with last(Value) makes it work but I'm not always certain with these self-joins...

I also convert the Date to as.Date and also renames it as Date() is a base function.


dt <- data.table(ContractID= c(1,1,1,2,2)
                 , Dates = as.Date(c("2018-02-01", "2018-02-20", "2018-03-12", "2018-02-01", "2018-02-12"))
                 , Value = c(10,20,30,10,20))

dt[dt[, .(ContractID, Dates, Dates_Lower = Dates - 30, Value)] #self-join
   ,on = .(ContractID = ContractID
          , Dates >= Dates_Lower
          , Dates <= Dates
   , j = .(ContractID, Dates, Value)
   , allow.cartesian = TRUE
   ][, j = .(Value = last(Value), Cum_Sum_1M = sum(Value))
     ,by = .(ContractID, Dates)
   ContractID      Dates Value Cum_Sum_1M
1:          1 2018-02-01    10         10
2:          1 2018-02-20    20         30
3:          1 2018-03-12    30         50
4:          2 2018-02-01    10         10
5:          2 2018-02-12    20         30

Cumulative SUM per day, week and month - Databases, I need to fetch the cumulative sum for sales made by a marketeer per day, week and month. from the weeks before but I also need the sales made per month, i.e. sales from the first day of the current week, which is a sunday, up to which means all days - and then you will always receive just one row� In the Apply Date Formatting dialog box, choose the month and year date format Mar-2001 that you want to use. See screenshot: 4. Click Ok to change the date format to month and year format, and then select the data range (A1:B15) that you want to work with, and go on clicking Kutools > Merge & Split > Advanced Combine Rows, see screenshot: 5.

This is an other working data.table solution..

dt[, Date := lubridate::ymd( Date ) ]
setkey(dt, Date)
dt[dt, Cum_Sum_1M := {
  val = dt[ ContractID == i.ContractID & Date %between% c( i.Date - months(1), i.Date ), Value];
  list( sum( val ) )
}, by = .EACHI ]
setkey(dt, ContractID, Date)


#    ContractID       Date Value Cum_Sum_1M
# 1:          1 2018-02-01    10         10
# 2:          1 2018-02-20    20         30
# 3:          1 2018-03-12    30         50
# 4:          2 2018-02-01    10         10
# 5:          2 2018-02-12    20         30

Solved: Running total until Today, Running total upto and including Today in current month (and year) = VAR RunningTotal = CALCULATE(sum('Liquidit�'[Liquidit�]); FILTER(ALL('Calendar'); � rt Liquidità = CALCULATE(sum('Liquidità'[Liquidità]);FILTER((ALL('Calendar'[Data].[Date]));'Calendar'[Data].[Date] <= MAX('Calendar'[Data].[Date]))) but it gives value until the end of the period and if i fildar the date i gel the running total only for the selected dates. i need the running total value stops at the current mont and returns

4 Ways to Calculate a Running Total With SQL, over 1 year ago Calculating a running total/rolling sum in SQL is a useful skill to have. This might be useful to compare how much profit a driver brings in day -over-day per month. using this method typically forces the query to scan the table for every row that exists in the original table all over again. I think that there is an efficiency difference and ROWS UNBOUNDED PRECEDING AND CURRENT ROW is to be preferred (after testing of course and if it gives the results you want). Much more details you can find in the blog article by @Aaron Bertrand , including performance tests: Best approaches for running totals – updated for SQL Server 2012

Solved: Cumulative Sum (MTD) Column for Month Using MySQL , I would like it to be grouped by day and by store, with a column for that I need a cumulative sum (MTD) for every store for every date. How would I make it so that the Sales MTD (cumulative) column is only summing for the current month? Before the statement you gave me, here is what I was trying:. Cumulative Total Sales := CALCULATE ( [Total Sales], FILTER ( ALL ( DimDate[Datekey] ), DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) ) )) Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed.

What I mean here is , the month selection will add up the values of previous months and current month here ..Cumulative sum for the current selection of month from the previous months. Any idea how can we do this in tableau. Regards, Anisur