Counting the last time a supplier transacted


(Woodbine) #1

I have some accounts payable transactions between a buyer and their suppliers. We want to monitor the last time a supplier transacted so that we can get rid of old suppliers in the database. I'm able to count the number of suppliers who transacted (and their total value), but I can't work out how to do a count on a max date by month.

Is this a situation where the best answer is to create a new field in the index?


(Tyler Smalley) #2

I would recommend using a data table, creating an aggregation on the min of the transaction date, then split the rows on the buyer.

Here is the sample data I used in Console:

POST /discuss-97878/transaction
{
  "created_at": "2017-05-01",
  "buyer": "foo"
}

POST /discuss-97878/transaction
{
  "created_at": "2017-04-01",
  "buyer": "foo"
}


# oldest foo
POST /discuss-97878/transaction
{
  "created_at": "2017-03-01",
  "buyer": "foo"
}

# oldest bar
POST /discuss-97878/transaction
{
  "created_at": "2017-05-02",
  "buyer": "bar"
}

POST /discuss-97878/transaction
{
  "created_at": "2017-06-02",
  "buyer": "bar"
}

POST /discuss-97878/transaction
{
  "created_at": "2017-07-02",
  "buyer": "bar"
}

POST /discuss-97878/transaction
{
  "created_at": "2017-06-03",
  "buyer": "baz"
}

POST /discuss-97878/transaction
{
  "created_at": "2017-05-03",
  "buyer": "baz"
}

POST /discuss-97878/transaction
{
  "created_at": "2017-07-03",
  "buyer": "baz"
}

Then, I setup the visualization:


(Woodbine) #3

Thank you that's perfect.


(system) #4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.