1

I have data where I am trying to calculate average sales of last 3 months from most recent order date and also want to calculate YTD sales of that customer and everything groupby customerID.In Orderstatus I only want to calculate Shipped and partially shipped orders.

My data looks like this:

OrderStatus              OrderDate    OrderTotal    CustomerID

Partially Shipped       7/5/2019       229.5           1
Shipped                 3/5/2019       48.35           1
Shipped                 3/6/2019       31.94           2
Shipped                 3/8/2019        91.8           1
Shipped                 3/8/2019       45.9             1
Shipped                 3/11/2019       98              2
Refunded                6/13/2019     22599             7
Shipped                 1/14/2019       0               1
Shipped                 5/15/2019        7              7
Shipped                 5/15/2019      48.5             7
Shipped                 12/15/2018     272.01           1
Shipped                 11/15/2018     34.97            1
Shipped                 11/1/2018      13.43            1
Shipped                 3/15/2018      34.97            1
Shipped                 4/15/2019      92.94            7
Shipped                 3/15/2018       0               1
Shipped                 3/17/2019     102.85            7
Shipped                 3/18/2019       49              7
Shipped                 3/18/2019     119.95            7
Shipped                 3/18/2019     58.96             7
Cancelled               3/19/2018     20.48             1
Shipped                 3/20/2019     14.6              7

I have tried aggregating it by customerid but in orderstatus i only want the records where status is shipped. I am not able to put that filter on and also dont know how to take only 3 months for average.

I tried this code:

x <- aggregate(x$Order.Total..inc.tax.~ x$Customer.ID,data=x,FUN=sum)

I am getting this error on running this:

Error in Summary.factor(c(1L, 1L, 1L, 1L, 1L, 1L), na.rm = FALSE) : ‘sum’ not meaningful for factors

output I am expecting is:

CustomerID     YTD(2019)     2018     Average
   1            415.55      355.38     138.5
   2            129.94                 43.31
   7            493.8                  164.6
jsingh
  • 199
  • 4
  • 12
  • The specific error has to do with one of your variables (likely `Order.Total..inc.tax.`) not being numeric. That means when you imported the data some numbers were imported as strings for some reason - probably extra whitespace. –  Jul 08 '19 at 17:26
  • @gersht..Thanks for the reply. I will try that , but can you please help me out how to calculate average for last 3 months? and get YTD sales? – jsingh Jul 08 '19 at 17:32
  • As Gsingh and gersht note, the code should work fine if you just convert your OrderTotals to numeric first. See [here](https://stackoverflow.com/questions/18045096/r-error-sum-not-meaningful-for-factors) for how to do that. If it still doesn't work after that, a reproducible example would be needed (you can use `dput` to provide the actual data rather than a printout) – IceCreamToucan Jul 08 '19 at 17:46
  • @IceCreamToucan..It worked,error is gone now but i need help how to calculate average for last 3 months? – jsingh Jul 08 '19 at 17:58
  • jsingh like IceCreamToucan recommended, you should use `dput` to provide us with an actual sample of your data. –  Jul 09 '19 at 08:06

1 Answers1

0

try converting the data from factors to numeric/integer.

Gsingh
  • 36
  • 4
  • This kind of one sentence partial solution isn't particularly helpful. Can you eloborate? Maybe with an example? –  Jul 08 '19 at 17:47