# How to count values based on date

6 views (last 30 days)
Zekeftw on 9 Jul 2017
Edited: Andrei Bobrov on 10 Jul 2017
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!
Zekeftw on 10 Jul 2017
Thank you all for your help. I went with a variation of both and ultimately got what i was looking for.
% Data
weeklySum = grpstats(Data(74:78,4:end),[],'sum'); % Sum weekly orders
monthlySum = weeklySum{1,2:end}; % Array of data for plot
% Plot
hbar = bar(monthlySum);
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

Image Analyst on 9 Jul 2017
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?
Zekeftw on 9 Jul 2017
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 on 9 Jul 2017
counts = grpstats(t(:,3:end), 'WeekOf___', @sum);
selected = counts(:,3:end);
selected.Properties.VariableNames = t.Properties.VariableNames(4:end);
selected

Andrei Bobrov on 10 Jul 2017
Edited: Andrei Bobrov on 10 Jul 2017
MATLAB R2016b and later
TT = table2timetable(T,'RowTimes','WeekOf___');
TT_out = retime(TT,'monthly','mean');
or MATLAB R2016a and eirliar
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]);

### Categories

Find more on Spreadsheets in Help Center and File Exchange

### Community Treasure Hunt

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

Start Hunting!