MATLAB Answers

Martin
0

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
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

Sign in to comment.

1 Answer

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

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.)
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
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.

Sign in to comment.