how to create calender

[anat]

Hi Friends,

if i select january month , data should fetch from 2nd jan 2016 to 1 st feb 2016

name	date	sal
a	11-Jan-16	10
b	20-Jan-16	20
c	1-Feb-17	30
a	11-Feb-16	40
b	20-Feb-16	50
c	1-Mar-17	60

as per above data

if i select Jan month, sum (sal) displaying  (10+20+30)=60 but chart level displaying month wise for jan (10+20)=30 and for feb 30 ,my requirement is it should display for jan month 60

same if i select feb ,sum(sal)=150(40+50+60),but chart level displaying month wise for feb(40+50)=90 and for mar 60,in this case as per my requirement it should display for feb month 150.

[anat]

@Team,any idea?

[Anonymous]

Hi,

This works for me as you explained above:

using a modification of code from rwunderlich‌ 's cook book and Qlikview Masters Summit resources:

Sample attached...

Sal: //***** Tablename
LOAD name,
date#(date,'DD-MMM-YY') as SAL_DATE,  //***** Fieldname
  sal
FROM
[https://community.qlik.com/thread/248237]
(html, codepage is 1252, embedded labels, table is @1);

QuartersMap: 
MAPPING LOAD 
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 

Temp: 
Load 
min(SAL_DATE) as minDate,  //***** Fieldname
  max(SAL_DATE) as maxDate  //***** Fieldname
Resident Sal;  //***** Tablename
 
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); 

* Updated to handle when month is 1
MasterCalendar: 
Load 
TempDate AS SAL_DATE,  //***** Fieldname
   week(TempDate) As Week, 
Year(TempDate) As Year, 
if(TempDate = MonthStart(TempDate),
if(Month(TempDate) = 1, 12, Month(TempDate)-1),
Month(TempDate)) As Month, 
Day(TempDate) As Day, 
YeartoDate(TempDate)*-1 as CurYTDFlag, 
YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 
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; 

[tracysmart]

Anat

Your chart is behaving as it should as 1st Feb is in February. It seems you want to offset your calendar by a day so a month always begins on the 2nd is that correct? and the 1st of every month belongs to the month before?

Are you using a separate calendar table at the moment?