I need turnover for last month from my table

Hello, I have a table like the one below:

summary =

6×2 table

Time Monthly_Turnover

___________________ _______________

'start' 0

'01-Jul-2019 17:00:39' 76

'24-Jul-2019 14:00:18' 56

'01-Aug-2019 16:00:35' 76

'15-Aug-2019 15:40:24' 98

'21-Aug-2019 13:50:01' 10

I need to get the sum of the Monthly_Turnover-column for a full month based on the timestamp in the first column (from today*). If the timestamp-column does only represent, say, two weeks, I only need the sum of those two weeks.

*) the timeformat is the same at the following line of code:

datestr(datetime('now'))
ans = 
   '21-Aug-2019 21:22:58'

In this case the result should be: 56+76+98+10 = 240

Does anyone have an idea how to proceed with such a problem? Thanks in advance

Answer 1

[Star Strider]

Try this:

dv = datetime({'01-Jul-2019 17:00:39'; '24-Jul-2019 14:00:18'; '01-Aug-2019 16:00:35'; '15-Aug-2019 15:40:24'; '21-Aug-2019 13:50:01'});
tv = [76; 56; 76; 98; 10];

T = table(dv, tv, 'VariableNames',{'Time','Monthly_Turnover'});
TT = table2timetable(T);
TTR = retime(TT, 'monthly','sum')

producing:

TTR =

  2×1 timetable

            Time            Monthly_Turnover
    ____________________    ________________

    01-Jul-2019 00:00:00          132       
    01-Aug-2019 00:00:00          184   

EDIT —

If you only want the month and year in ‘TTR’:

TT = table2timetable(T);
TT.Time.Format = 'MMM-yyyy';
TTR = retime(TT, 'monthly','sum')

produces:

TTR =

  2×1 timetable

      Time      Monthly_Turnover
    ________    ________________

    Jul-2019          132       
    Aug-2019          184

[Martin]

thanks a lot! It takes full month which is ok. But I would prefer it went one month back from the now-time

[Martin]

So if you have this now time

datestr(datetime('now'))
ans = 
   '21-Aug-2019 21:22:58'

I would like it to take from

   '21-Jul-2019 21:22:58' to '21-Aug-2019 21:22:58'

Instead, and the last month turnover is thus 56+76+98+10 = 240

[Star Strider]

As always, my pleasure.

I’m not sure what you mean by ‘one month back’. If you want to subtract a month from the results, this works:

TTR = retime(TT, 'monthly','sum')
TTR.Time = TTR.Time - calmonths(1)

producing:

TTR =

  2×1 timetable

      Time      Monthly_Turnover
    ________    ________________

    Jun-2019          132       
    Jul-2019          184 

If you intend a different result, please clarify. It may be possible.

[Martin]

I mean that if now time is

datestr(datetime('now'))
  ans=
   '21-Aug-2019 21:22:58'

Then I need all the turnover between

'21-Jul-2019 21:22:58' and '21-Aug-2019 21:22:58'

In my above example in my table that will give 56+76+98+10 = 240 due to it starts from '24-Jul-2019 14:00:18':

'24-Jul-2019 14:00:18' 56

'01-Aug-2019 16:00:35' 76

'15-Aug-2019 15:40:24' 98

'21-Aug-2019 13:50:01' 10

In other words, I dont need month like 1.Jan to 31. Jan, or 1. Feb to 29. Feb, or 1. Mar to 31 Mar and so on...

Note however, if there is only, say, 3 days or maybe 14 days (lesser than one month) in the table, it should just take the sum of all.

[Star Strider]

I’m not sure all those options are possible.

This adds 21 days to ‘TT.Time’, then calculates the 'monthly' sum using the new day-shifted definition of ‘month’:

T = table(dv, tv, 'VariableNames',{'Time','Monthly_Turnover'});
TT = table2timetable(T);
TT.Time.Format = 'MMM-yyyy';
TT.Time = TT.Time + caldays(21)
TTR = retime(TT, 'monthly','sum')

producing:

TT =

  5×1 timetable

      Time      Monthly_Turnover
    ________    ________________

    Jul-2019           76       
    Aug-2019           56       
    Aug-2019           76       
    Sep-2019           98       
    Sep-2019           10       


TTR =

  3×1 timetable

      Time      Monthly_Turnover
    ________    ________________

    Jul-2019           76       
    Aug-2019          132       
    Sep-2019          108 

The retime function can only use months (in this context, other fixed periods such as 'daily' and 'weekly' and such are possible), however it seems to be possible to shiift the definition of ‘month’ by a given number of days.

Experiment to see what other options are available. Subtracting 21 days just involves changing the sign in:

    TT.Time = TT.Time + caldays(21)

so you can add or subtract different numbers of days to get the result you want.

[Martin]

Okay thanks, I think its good enough, very helpful! I have another problem in the same topic if I may ask you. I have af third column in which I have eirther a 1, 0 or -1 in all the rows. I need to count how many 1's there is out of 1's and 0's (where -1's are ignored). So I get a percent for each month.

I tried with

retime(TT, 'monthly','mean')

But this one doesn't give me the accurate percentage.

To make it simple imagine week instead of month: If I have

1
1
0
0
0
-1
-1

The result should be 2 / ( 2 + 3 ) = 0,4 (for this week)

(due to 2 x 1's and 3 x 0's)

But clearly the 'mean' will give = 0 in this case.

[Star Strider]

As always, my pleasure.

I am not certain that I understand.

This is as close as I can get:

dv = datetime({'01-Jul-2019 17:00:39'; '24-Jul-2019 14:00:18'; '01-Aug-2019 16:00:35'; '15-Aug-2019 15:40:24'; '21-Aug-2019 13:50:01'});
tv = [76; 56; 76; 98; 10];
pv = [1; 1; 0; -1; -1];

T = table(dv, tv, pv, 'VariableNames',{'Time','Monthly_Turnover','Something_Else'});
TT = table2timetable(T);
TT.Time.Format = 'MMM-yyyy';
TT.Time = TT.Time + caldays(21)
% TTR = retime(TT, 'monthly','sum')
TT.Something_Else(TT.Something_Else < 0) = 0; 
TTR = retime(TT, 'monthly','mean')

This first sets the negative numbers to 0, then takes the mean. (I had to truncate ‘pv’ to fit the existing table.)

[Martin]

Unfortunately not completely correct. If I use this (I altered a little bit)

dv = datetime({'01-Jul-2019 17:00:39'; '24-Aug-2019 14:00:18'; '01-Aug-2019 16:00:35'; '15-Aug-2019 15:40:24'; '21-Aug-2019 13:50:01'});
tv = [76; 56; 76; 98; 10];
pv = [1; 1; 0; -1; -1];
T = table(dv, tv, pv, 'VariableNames',{'Time','Monthly_Turnover','Something_Else'});
TT = table2timetable(T);
TT.Time.Format = 'MMM-yyyy'
TT.Something_Else(TT.Something_Else < 0) = 0; 
TTR = retime(TT, 'monthly','mean')

The result should rather be the following:

TT =
  5×2 timetable
      Time      Monthly_Turnover    Something_Else
    ________    ________________    ______________
    Jul-2019           76                  1      
    Aug-2019           56                  1      
    Aug-2019           76                  0      
    Aug-2019           98                 -1      
    Aug-2019           10                 -1 
    
TTR =
  2×2 timetable
      Time      Monthly_Turnover    Something_Else
    ________    ________________    ______________
    Jul-2019           76                   1     
    Aug-2019           60                 0.5

0.5 because the -1 one should not count in as percentage. Your result gives 0.25 due to it counts the -1 in

[Star Strider]

When I run my code (in R2019a, Update 5):

dv = datetime({'01-Jul-2019 17:00:39'; '24-Jul-2019 14:00:18'; '01-Aug-2019 16:00:35'; '15-Aug-2019 15:40:24'; '21-Aug-2019 13:50:01'});
tv = [76; 56; 76; 98; 10];
pv = [1; 1; 0; -1; -1];                                   % Added Later

T = table(dv, tv, pv, 'VariableNames',{'Time','Monthly_Turnover','Something_Else'});
TT = table2timetable(T);
TT.Time.Format = 'MMM-yyyy';
TT.Time = TT.Time + caldays(21)
% TTR = retime(TT, 'monthly','sum')
TT.Something_Else(TT.Something_Else < 0) = 0;           % Added Later
TTR = retime(TT, 'monthly','mean')                      % Added Later

I get:

TT =

  5×2 timetable

      Time      Monthly_Turnover    Something_Else
    ________    ________________    ______________

    Jul-2019           76                  1      
    Aug-2019           56                  1      
    Aug-2019           76                  0      
    Sep-2019           98                 -1      
    Sep-2019           10                 -1      


TTR =

  3×2 timetable

      Time      Monthly_Turnover    Something_Else
    ________    ________________    ______________

    Jul-2019           76                  1      
    Aug-2019           66                0.5      
    Sep-2019           54                  0      

This appears to be what you want. Do you get a different result? (Note that the 'mean' argument aplies to all table variables. If you want the 'sum' for 'Monthly_Turnover', you will need to run that separately. It does not appear to be possible to do different things with different variables, at least as I read the documentation.)

[Martin]

Okay, I found that if I use your condition

TT.Something_Else(TT.Something_Else < 0) = 0; 

and alter it to

TT.Something_Else(TT.Something_Else < 0) = NaN; 

it actually fits my needs perfectly. Once again, thank you a lot for taking your time to help me! I appreciate it a lot

[Star Strider]

As always, my pleasure.

I initially experimented with setting ‘Something_Else’ to NaN for values <0 , and using the nanmean function, however it did not give what I understood to be the desired result. I may not have completely understood what you wanted.