I need turnover for last month from my table

Asked by Martin on 21 Aug 2019

Latest activity Commented on by Star Strider on 22 Aug 2019

Accepted Answer by Star Strider

1 view (last 30 days)

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

0 Comments

Products

MATLAB

1 Answer

Link

Answer by Star Strider on 21 Aug 2019

Edited by Star Strider on 21 Aug 2019

Accepted Answer

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

11 Comments

Show 8 older comments

Star Strider on 22 Aug 2019

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 on 22 Aug 2019

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 on 22 Aug 2019

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.

Products

MATLAB

You are now following this question

Martin (view profile)