Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Editing Start and End Dates for Months

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!

8 Replies
ramoncova06
Specialist III
Specialist III

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

Not applicable
Author

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?

ramoncova06
Specialist III
Specialist III

yes

Not applicable
Author

I could certainly create one easily enough. Do you know if that will affect my master calendar?

ramoncova06
Specialist III
Specialist III

Shouldn't ... in fact this should be part of your master calendar

Not applicable
Author

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; 

Not applicable
Author

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 🙂

ramoncova06
Specialist III
Specialist III

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;