topic Re: Master Calendar Date issue in QlikView

Master Calendar Date issue

Anonymous — Sun, 07 Dec 2014 19:19:46 GMT

Hi All,

Find my application data Model. In that I need to use a master calendar date for all the charts. However I am having Master calendar script readily I am not able to get the proper values.

Ex:

If domain having date from Jan01,Jan 02...Jan30, when I am using master Calendar date I am getting some other date(Feb 14)

By seeing my data modle kinldy let me know how to solve this issue

Re: Master Calendar Date issue

Anonymous — Sun, 07 Dec 2014 19:29:01 GMT

Hi,

Please can u copy & paste Master Calendar script here.

So will try to help.

Regards

Neetha

Re: Master Calendar Date issue

Anonymous — Mon, 08 Dec 2014 05:46:29 GMT

Hi Neetha,

My script will go like this

Link_Table:

Load SLD_KEY,

SLD_DT As Create_Date

Resident SLD;

cONCATENATE

Load Version_Key,

Version_Date As Create_Date

Resident Vesion;

Concatenate

Load SLA_KEY

SLA_Date as Create_Date

Resident SLA;

//*****Calendar*****////

Let V_mindate=Num(Date('05/01/2014'));

Let V_Maxdate=Num(Today());

Temp_Cal:

Load $(V_mindate)+ITERNO() As Num

Date($(V_mindate)+ITERNO()-1) As Temp_Date

Autogenerate (1) While

($(V_mindate)+ITERNO()-1 <= $(V_Maxdate);

Calendar:

Load Temp_Date As Create_Date

Resident Temp_Cal;

Drop Table Temp_Cal;

Re: Master Calendar Date issue

Anonymous — Mon, 08 Dec 2014 14:25:02 GMT

Hi All,

Any idea/Suggestion on this?

-Jay

Re: Master Calendar Date issue

Anonymous — Mon, 08 Dec 2014 20:38:48 GMT

Hi,

Please use the script below to create a master calendar.

// Please update your DATECOLUMN and Source table Name (SOURCETABLE)

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(DATECOLUMN) as minDate,

max(DATECOLUMN) as maxDate

Resident SOURCETABLE

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 DATECOLUMN,

week(TempDate) As Week,

Year(TempDate) As Year,

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;

Note:you can change according to your requirements.

Regards

Neetha

Re: Master Calendar Date issue

Anonymous — Tue, 09 Dec 2014 08:31:19 GMT

Hi Neetha,

Still I am having same issue.The master calendar date is not matched with source table dates. As I am using date as Dim I need to show this

-Jay

Re: Master Calendar Date issue

Anonymous — Wed, 10 Dec 2014 08:22:38 GMT

Hi,

Please can you post your modified script.

Regards

Neetha

Re: Master Calendar Date issue

Anonymous — Wed, 10 Dec 2014 08:30:30 GMT

Hi,

Attached sample application which contains the original table structure.

Thanks,

Jay

Re: Master Calendar Date issue

ferha_jafri — Wed, 10 Dec 2014 08:31:21 GMT

Did you check the formats of Dates in which format do you have in the database and in qvw did you gave the format of Date which you want in the Date variable declared in the start , this issue usually happen when the Date formats did not match.

Re: Master Calendar Date issue

Anonymous — Wed, 10 Dec 2014 08:56:33 GMT

Hi,

please copy& paste the code here.

So can help.

Regards

Neetha

Re: Master Calendar Date issue

simenkg — Wed, 10 Dec 2014 09:01:59 GMT

You have an extra parenthesis at the start of your while statement.

Re: Master Calendar Date issue

Anonymous — Wed, 10 Dec 2014 09:31:38 GMT

Hi Ferha,

In my DB tables the date field is in YYY-MM-DD format, but I am changing the format into MM/DD/YYYY in my qvw. So date format looks fine

Re: Master Calendar Date issue

Anonymous — Wed, 10 Dec 2014 09:39:03 GMT

Hi Simen,

I am not able to identify. Can you enlighten on this?

Re: Master Calendar Date issue

simenkg — Wed, 10 Dec 2014 09:47:25 GMT

Autogenerate (1) While

($(V_mindate)+ITERNO()-1 <= $(V_Maxdate);

Autogenerate (1) While

$(V_mindate)+ITERNO()-1 <= $(V_Maxdate);

Re: Master Calendar Date issue

Wed, 10 Dec 2014 09:55:14 GMT

Instead of using a WHILE i use variable called vRange, which subtracts from your end date to your start date.

Please see example below.

LET vStart = Peek('StartDate',0,'DateRange')-1;

LET vEnd = Peek('EndDate',0,'DateRange')-0 ;

LET vRange = $(vEnd) - $(vStart);

TempDate:

LOAD

$(vStart) + RecNo() AS DateNum

AUTOGENERATE $(vRange);

CALENDAR:

LOAD

AutoNumber(Period, 'PeriodID') AS [PeriodID],

AutoNumber(Year & Quarter, 'QuarterID') AS [QuarterID]

;

LOAD Distinct

Date(DateNum) AS Date,

Year(DateNum) AS Year,

Month(DateNum) AS Month,

Year(DateNum) * 100 + Month(DateNum) AS [Period],

'Q'&

If(Match(Month(DateNum),'Jan','Feb','Mar') > 0 , 4,

If(Match(Month(DateNum),'Apr','May','Jun') > 0 , 1,

If(Match(Month(DateNum),'Jul','Aug','Sep') > 0 , 2, 3))) AS Quarter,

MonthName(DateNum) AS Month_Year,

Day(DateNum) AS Day,

WeekDay(DateNum) AS WeekDay,

Year2Date(DateNum,0) AS ThisCalendarYTD,

YearName(DateNum,0,4) AS LastCalendarYTD,

Year2Date(DateNum,0,4) AS ThisFiscalYTD,

Year2Date(DateNum,-1,4) AS LastFiscalYTD

RESIDENT TempDate;

DROP TABLE DateRange;

DROP TABLE TempDate;

Re: Master Calendar Date issue

Anonymous — Wed, 10 Dec 2014 18:38:59 GMT

Hi Neetha,

Find the original Datamodel in the application

Domain:

LOAD * INLINE [Domain_ID, Creation_Date_1, Updated_Date_1

];

Services:

LOAD * INLINE [Domain_ID,Service_Unique_id,Creation_Date_2,Updated_Date_2

];

Version:

LOAD * INLINE [Service_Unique_id,SLD_Unique_Id,Creation_Date_3,Updated_Date_3

];

SLA:

LOAD * INLINE [SLA_UId,SLD_Unique_Id,Creation_Date_4,Updated_Date_4

];

App_Ver:

LOAD * INLINE [SLA_UId,AV_ID,Creation_Date_5,Updated_Date_5

];

Buss:

LOAD * INLINE [AV_ID,Creation_Date_6,Updated_Date_6

];

SLD:

LOAD * INLINE [SLD_Unique_Id,E_ID,Creation_Date_7,Updated_Date_7

];

END:

LOAD * INLINE [E_ID,Creation_Date_8,Updated_Date_8

];

Re: Master Calendar Date issue

Anonymous — Wed, 10 Dec 2014 19:35:15 GMT

hi ij,

Please can you copy paste source table dates format.

what are source table dates format : DD/MM/YYYY (or) DD/MM/YYYY hh:mm:ss.

if DD/MM/YYYY hh:mm:ss ,format with below code in script for source table dates:

Date(Floor(Fieldname)) as Fieldname

Regards

Neetha