Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to monitor the number of products sold per month for the company I work for. The data I have has dates for each sales transaction. My trouble is that the "business month" goes from the 28th of one month until the 27th of the following month. Therefore, using the master calendar to create months means that the months generated don't align with the months I'm actually trying to measure. Do you know how I can edit the months so that "January" corresponds to December 28 - January 27th, for example?
Thanks!
do you have any specific way to identify when a period started and ended ?
based on that you could do an applymap to define your month periods
Do you mean something like another column (or sheet) in my original excel document where my data is stored that lists how dates correspond to months?
yes
I could certainly create one easily enough. Do you know if that will affect my master calendar?
Shouldn't ... in fact this should be part of your master calendar
I used the following text to create the master calendar: Do I need to change any of this to make the 28th - 27th months work?
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min([Case Created Date]) as minDate,
max([Case Created Date]) as maxDate
Resident [Company Cases];
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS [Case Created Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Hi,
In master calendar just add these these two fields :
if(day(TempDate) >= 28, month(AddMonths(TempDate,1)), month(TempDate)) as Month_Mod
and
if(day(TempDate) >= 28, Year(AddMonths(TempDate,1)), Year(TempDate)) as Year_Mod
In the expression just use this
sum( {<Year = , Month =, Month_Mod = {$(=max(Month ))} , Year_Mod = {$(=max(Year))}>} Sales)
hope it works 🙂
it really depends on how your dates are set up, but something like this should work
BusinessMapPeriod:
MAPPING LOAD
Date,
Month as BusinessPeriod
from yoursource;
MasterCalendar:
Load
TempDate AS [Case Created Date],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
if(ApplyMap('BusinessMapPeriod',TempDate,'No') <> 'No',ApplyMap('BusinessMapPeriod',TempDate), peek(BusinessMonth) As BusinessMonth
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;