Creating an "over time" algorithm

Question

What I have is a LineItem object and a collection called LineItems that holds LineItem objects. My LineItem objects have a property called ItemDate (DateTime data type) and another property called ItemAmount (decimal data type) and another property called ItemCategory (integer data type).

What I am trying to do is write a function in my collection object that will return the integer category in whichever category has the highest sum(ItemAmount) of that category. For example, if I had this:

Object 1:

ItemAmount=50.00

ItemCategory=1

Object 2:

ItemAmount=25

ItemCategory=1

Object 3:

ItemAmount=535.00

ItemCategory=2

OK so in this example, the function should return 2 since ItemCategory 2 had a bigger ItemAmount (535) than ItemCategory 1 (50+25 = 75).

This I know how to do. What makes it harder though is I need it to be able to do this over time, meaning, if an amount has a large amount, like 535, but some other objects have smaller amounts, but there are more of them to closer dates, then it needs to be techically "larger" than the other amount. It is almost like the amount needs to take into account frequency too. $10 amount occuring everyday needs to be "larger" than $500 occuring once a year, even if the $500 is larger than $10 every day. It is almost like I need to look at a month of data through a year's magnifing glass.

I hope this question makes sense.

Answer 1

Try this:

class ListItem
{
    internal DateTime When;
    internal decimal Amount;
    internal int Category;
}

class ListItems
{
    internal List<ListItem> _items = new List<ListItem>();
    internal int GetCategory(DateTime from, DateTime to)
    {
        Dictionary<int, decimal> totals = _items
            .Where(y => y.When >= from && y.When < to)
            .GroupBy(x => x.Category)
            .ToDictionary(
                category => category.Key,
                category => category.Sum(item => item.Amount)
            );
        return totals.Keys
            .Where(category => totals[category] == totals.Values.Max())
            .First();
    }
}

The Linq's a bit of a mouthful, but if I understand the question correctly, I think it'll work.

Answer 2

Sounds like you might want a traveling window sum: pick a time period (say, 30 days), and track the total of items within that window.

If you just want a single instance (say, 30 days ending right now), of course all you need to do is sum up those items within the window. But, if you want to plot a graph, it is most efficient to sort your items, and perform a running accumulation; add items coming into the window, and subtract items leaving it.

To implement a traveling window, you can use two iterators into a sorted list (the following is pseudocode, not C#):

// initialize
acc= 0.0
for(startP= SortedItems.begin; !startP.last; startP.next) {
  if(startP.item.time > graphstart - timewindow) { break }
}
for(endP= startP; !endP.last; endP.next) {
  acc += endP.item.value
  if(endP.item.time > graphstart) { break }
}

// main loop
for(; !endP.last; endP.next) { // advance time window to next item
  endItem= endP.item
  endTime= endItem.time
  for(; !startP.last; startP.next) { // subtract items leaving time window
    startItem= startP.item
    startTime= startItem.time
    if(startTime > endTime - timewindow) { break }
    acc -= startItem.value
    output(startTime + timewindow, acc)  // register graph values coming down
  }
  acc += endItem.value
  output(endTime, acc)  // register graph value going up
}