Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
anat
Creator
Creator

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

@Team,any idea?

qlikdeez
Specialist
Specialist

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?