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: 
anat
Master
Master

how to create calender

Hi Friends,

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

   

namedatesal
a11-Jan-1610
b20-Jan-1620
c1-Feb-1730
a11-Feb-1640
b20-Feb-1650
c1-Mar-1760

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.

3 Replies
anat
Master
Master
Author

@Team,any idea?

Anonymous
Not applicable

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
Creator II
Creator II

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?