0

I need to replicate - or at least find an alternative solution - for a SUMIFS function I have in Excel.

I have a transactional database:

SegNbr  Index   Revenue SUMIF
A         1       10     30
A         1       20     30
A         2       30     100
A         2       40     100
B         1       50     110
B         1       60     110
B         3       70     260
B         3       80     260

and I need to create another column that sums the Revenue, by SegmentNumber, for all indexes that are equal or less the Index in that row. It is a distorted rolling revenue as it will be the same for each SegmentNumber/Index key. This is the formula is this one: 

=SUMIFS([Revenue],[SegNbr],[@SegNbr],[Index],"<="&[@Index])
MrFlick
  • 195,160
  • 17
  • 277
  • 295
user3469390
  • 55
  • 6
  • 1
    Please make a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output. Are indexes sorted (or can they be)? Do you really just need a cumulative sum per segment? – MrFlick Dec 09 '14 at 00:05
  • The indexes can be sorted. No, I don't need a simple cumulative sum nor a rolling cumulative sum. It is something in the middle. I don't know if this helps, but I'm using this to create a cumulative distribution for each segment and am using the revenue as a weight. – user3469390 Dec 09 '14 at 00:38

1 Answers1

1

Let's say you have this sample data.frame

dd<-read.table(text="SegNbr  Index   Revenue
A         1       10
A         1       20
A         2       30
A         2       40
B         1       50
B         1       60
B         3       70
B         3       80", header=T)

Now if we make sure the data is ordered by segment and index, we can do

dd<-dd[order(dd$SegNbr, dd$Index), ]  #sort data
dd$OUT<-with(dd, 
    ave(
        ave(Revenue, SegNbr, FUN=cumsum),  #get running sum per seg
        interaction(SegNbr, Index, drop=T), 
    FUN=max, na.rm=T) #find largest sum per index per seg
)
dd

This gives

  SegNbr Index Revenue OUT
1      A     1      10  30
2      A     1      20  30
3      A     2      30 100
4      A     2      40 100
5      B     1      50 110
6      B     1      60 110
7      B     3      70 260
8      B     3      80 260

as desired.

MrFlick
  • 195,160
  • 17
  • 277
  • 295