topic Re: how to create calender in QlikView

how to create calender

anat — Wed, 01 Feb 2017 08:08:35 GMT

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.

Re: how to create calender

anat — Wed, 01 Feb 2017 08:49:47 GMT

@Team,any idea?

Re: how to create calender

Anonymous — Wed, 01 Feb 2017 09:13:07 GMT

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;

Re: how to create calender

tracysmart — Wed, 01 Feb 2017 09:14:02 GMT

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?