Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
Valued Contributor III

Re: Editing Start and End Dates for Months

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

Re: Editing Start and End Dates for Months

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
Valued Contributor III

Re: Editing Start and End Dates for Months

yes

Not applicable

Re: Editing Start and End Dates for Months

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

ramoncova06
Valued Contributor III

Re: Editing Start and End Dates for Months

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

Not applicable

Re: Editing Start and End Dates for Months

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

Re: Editing Start and End Dates for Months

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
Valued Contributor III

Re: Editing Start and End Dates for Months

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;

Community Browser