How to count values based on date

2017 7 9
3 个回答
更新时间：2017 7 10
8 次查看（30 天）

0 个投票

I'm trying to count how many orders each customer placed each month. The attached spreadsheet has the data for weekly orders in this format:
Sunday Saturday Week Of Andrew Monica Ana
5/28/17 6/3/17 5/28/17 36 37 10
6/4/17 6/10/17 6/4/17 31 51 17
6/11/17 6/17/17 6/11/17 40 36 11
6/18/17 6/24/17 6/18/17 49 55 21
6/25/17 7/1/17 6/25/17 41 46 21
7/2/17 7/8/17 7/2/17 29 27 12
I need to count how many total orders each user placed in June (for example). I have no experience working with dates. Any help would be greatly appreciated.
Thanks!

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Zekeftw
Zekeftw 2017-7-10
Thank you all for your help. I went with a variation of both and ultimately got what i was looking for.
% Data
Data = readtable('Customer_Orders_Weekly.xls');% Read Data
weeklySum = grpstats(Data(74:78,4:end),[],'sum'); % Sum weekly orders
headers = Data(1,4:end).Properties.VariableNames; % Get headers for xLabels
monthlySum = weeklySum{1,2:end}; % Array of data for plot
% Plot
hbar = bar(monthlySum);
xLabels = headers;
set(gca, 'XTick', 1:21, 'XTickLabel', xLabels);
xtickangle(45);
% Data labels
x = get(hbar,'XData');
y = get(hbar,'YData');
ygap = 0.1;
ylimits = get(gca,'YLim');
set(gca,'YLim',[ylimits(1),ylimits(2)+0.2*max(y)]);
dataLabels = cellstr(num2str(monthlySum')); %//'
for i = 1:length(x) % Loop over each bar
xpos = x(i); % Set x position for the text label
ypos = y(i) + ygap; % Set y position, including gap
htext = text(xpos,ypos,dataLabels{i}); % Add text label
set(htext,'VerticalAlignment','bottom', 'HorizontalAlignment','center')
end

请先登录，再进行评论。

请先登录，再回答此问题。

回答（3 个）

Image Analyst
Image Analyst 2017-7-9

0 个投票

Try readtable(), and grpstats() in the Stats tool box.
but some of your start and stop times cross the first day of the month and there is no breakdown of what counts should be allocated to each month. So what do you want to do in that case?

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Zekeftw
Zekeftw 2017-7-9
I'm mostly looking for an average and trend so the overlap is not a big deal. I'm mostly trying to count to totals based on the "Week Of" column. For every week that covers June (for example)

请先登录，再进行评论。

Walter Roberson
Walter Roberson 2017-7-9

0 个投票

t = readtable('Customer_Orders_Weekly.xls');
counts = grpstats(t(:,3:end), 'WeekOf___', @sum);
selected = counts(:,3:end);
selected.Properties.VariableNames = t.Properties.VariableNames(4:end);
selected
Andrei Bobrov
Andrei Bobrov 2017-7-10
编辑：Andrei Bobrov 2017-7-10

0 个投票

MATLAB R2016b and later
T = readtable('Customer_Orders_Weekly.xls');
TT = table2timetable(T,'RowTimes','WeekOf___');
TT_out = retime(TT,'monthly','mean');
or MATLAB R2016a and eirliar
T = readtable('Customer_Orders_Weekly.xls');
T.mounth = month(TT.WeekOf___);
T.year = year(TT.WeekOf___);
T_out = varfun(@mean,T,'GroupingVariables',{'mounth','year'});
T_out = T_out(:,[1:2,7:end]);

类别

在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息

产品

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by