topic Re: Master calendar and Fact table dates in QlikView

Master calendar and Fact table dates

Anonymous — Mon, 15 Jun 2015 10:39:05 GMT

Hello

Can somebody help me with the below query regarding Master Calendar?

I tried using the below script to generate a master calendar. I am not sure what's going wrong with it. May be I am missing out something

I need to display tickets raised between a certain time period i.e between start and end dates.

I also need to display zero values for dates where there were no tickets raised

So i made changes to the script like below but when I plot the graph I dont get to see the blank values for those weeks where there were no tickets raised.

QuartersMap:

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

Temp:
Load
min([Release Start Date]) as minDate,
max([Release End Date]) as maxDate
Resident Release_dates;

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 [Ticket Raised Time (GMT)],

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

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: Master calendar and Fact table dates

jpenuliar — Mon, 15 Jun 2015 12:28:52 GMT

Hi Sanjyot,

What exactly is wrong with the script you are using?

Re: Master calendar and Fact table dates

rajeshvaswani77 — Mon, 15 Jun 2015 12:34:07 GMT

Hi Sanjyot,

Can you please elobrate?

thanks,

Rajesh Vaswani

Re: Master calendar and Fact table dates

Anonymous — Mon, 15 Jun 2015 12:52:16 GMT

Hi,

I'm not sure if I understand what do you need but the matsercalendar can be (with facts date field name = Date01:

let varMinDate = num(peek('Date01',0,'Facts'));
let varMaxDate = num(peek('Date01',-1,'Facts'));

TmpCalendar:

Load

date($(varMinDate) + rowno() -1) as TempDate
autogenerate $(varMaxDate) - $(varMinDate) +1;

MasterCalendar:
LOAD
date(TempDate) AS Date01,

Year(TempDate) AS Year,
week(TempDate) AS Week,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
'Q' & ceil(month(TempDate)/3) as Quarter,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
weekday(TempDate) AS WeekDay,
Resident
TmpCalendar
ORDER BY
TempDate Asc;

DROP TABLE TmpCalendar;

Re: Master calendar and Fact table dates

Anonymous — Tue, 16 Jun 2015 06:28:25 GMT

Hello

I need to display all values of tickets raised between a particular start date and end dates even if there are no tickets raised for a particular date. Currently ticket values are shown only if there is a date raised . If there was no ticket raised for a particulare date this values doesnt appear in the graph as zero as the date doesnt exist but I need to display tickets values sequentially as per dates irrespective of the date being present or not

Start Date - 12 Jan 2015

End Date - 18n June 2015

Ticket raised date is to be displayed chronologically between the above range dates if there may be no date specified in the database. Hence I populated release start date and release end date in mindate and max date and ticket raised time under TempDate. Please let me know what I am doing incorrectly

QuartersMap:

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 [Ticket Raised Time (GMT)],

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

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: Master calendar and Fact table dates

tracysmart — Tue, 16 Jun 2015 08:32:42 GMT

what type of chart are you using?

Have you unchecked Suppress Zero values on the presentation tab?

Re: Master calendar and Fact table dates

Anonymous — Tue, 16 Jun 2015 08:51:49 GMT

Hi,

I think is better if you post a test QVW file, is better for us to help you.

Regards.

Re: Master calendar and Fact table dates

Anonymous — Wed, 17 Jun 2015 11:36:14 GMT

I have unchecked Suppress Zero values. Using a bar chart.

Re: Master calendar and Fact table dates

Anonymous — Wed, 17 Jun 2015 11:45:19 GMT

!Hi

since I am using a personal edition I am pasting the script below. unable to attach qvw file.

LOAD
    Release#,
       Status,
     Duration,
     [Impacted Tools],
     Severity,
     [Ticket Raised Time (GMT)],
     [Ticket Resolved Time (GMT)],
          Created,
       if(Status = 'Closed',
      if(Duration > 0 and [Ticket Raised Time (GMT)]>'' and [Ticket Response Time (GMT)]>'' ,
      if(Severity = '1' and ([Ticket Response Time (GMT)] - [Ticket Raised Time (GMT)])*24 <=1 , 'SLA_MET',
   if(Severity = '2' and ([Ticket Response Time (GMT)] - [Ticket Raised Time (GMT)])*24 <=1,'SLA_MET',
   if(Severity = '3' and ([Ticket Response Time (GMT)] - [Ticket Raised Time (GMT)])*24 <=2, 'SLA_MET','SLA_NOT_MET'))))) as SLA_Indicator,

         [Ticket Response Time (GMT)],
     [Ticket Closed Time (GMT)],
       ID,
      SubField(Environment,';') as Environment,

      if([Root Cause] like '*App*', 'App',
     if([Root Cause] like '*Env*','Env')) as Root_Cause

FROM [Dashboard/NewDashboard/W.xlsx];

I am expected to calculate the tickets raised enviornment downtime for a particular release

Calculation of downtime in Expression is given in the syntax as below -

sum

({<Environment = {"A*",'ALL'}, Severity ={1,2}, Status ={'Closed','In Progress','Pending For User Verification'}, Root_Cause ={'App'}>}Duration) /118.5

I need to display zero values for the missing weeks between 20 April 2015 till 19 Sept 2015 even if there were nil requests.

Re: Master calendar and Fact table dates

Anonymous — Wed, 17 Jun 2015 11:51:21 GMT

I'm sorry but without QVW and a sample source data file is very difficult to try to help you for me.

I hope other person have any idea.

Regards.

Re: Master calendar and Fact table dates

kuczynska — Wed, 17 Jun 2015 12:07:22 GMT

I think I was facing similar issue at some point. I tried to change the expression in the bar chart as follows:

count(#Tickets) + 0.001

and changing the value type in the Number tab to something with a smaller resolution - integer for example (I found that solution somewhere in Qlik Community). It worked for me and additional bars with value 0 were displayed on my bar chart. Maybe that will help.

Re: Master calendar and Fact table dates

Anonymous — Thu, 18 Jun 2015 11:50:19 GMT

I tried that but its not working.I dont know whats going wrong as the script seems fine to me.

Re: Master calendar and Fact table dates

Anonymous — Thu, 18 Jun 2015 11:51:16 GMT

I have pasted the script above.
Let me know what else you need.

Re: Master calendar and Fact table dates

Anonymous — Thu, 18 Jun 2015 11:57:20 GMT

Release_dates:
LOAD Release as Release#,
Environment,
New_Environment,
[Release Start Date]

FROM
[Release Dates].xls

Tickets:

LOAD
     Release#,

     Status,
     Duration,
     [Impacted Tools],
     Severity,
     [Ticket Raised Time (GMT)],
     [Ticket Resolved Time (GMT)],
     [Raised By],
         Created,
     [Issue ID],
              if(Status = 'Closed',
      if(Duration > 0 and [Ticket Raised Time (GMT)]>'' and [Ticket Response Time (GMT)]>'' ,
      if(Severity = '1' and ([Ticket Response Time (GMT)] - [Ticket Raised Time (GMT)])*24 <=1 , 'SLA_MET',
   if(Severity = '2' and ([Ticket Response Time (GMT)] - [Ticket Raised Time (GMT)])*24 <=1,'SLA_MET',
   if(Severity = '3' and ([Ticket Response Time (GMT)] - [Ticket Raised Time (GMT)])*24 <=2, 'SLA_MET','SLA_NOT_MET'))))) as SLA_Indicator,

     ,
     [Root Cause],

     [Ticket Response Time (GMT)],
     [Ticket Closed Time (GMT)],

     ID,
      SubField(Environment,';') as Environment,

,
      if([Root Cause] like '*App*', 'App',
     if([Root Cause] like '*Env*','Env')) as Root_Cause

FROM
Ticket.xls
)) ;

This is my script

My Dimension for the chart is

if(weekstart([Ticket Raised Time (GMT)])>=weekstart([Release Start Date]) and WeekStart([Ticket Raised Time (GMT)])<=weekstart([Release End Date]),weekstart([Ticket Raised Time (GMT)]))

My Expression for the chart is

For App -

sum

({<New_Environment = {"UTE*",'ALL'}, Severity ={1,2}, Status ={'Closed','In Progress','Pending For User Verification'}, Root_Cause ={'App'}>}Duration) /118.5

For Env -

sum

({$<New_Environment = {"UTE*",'ALL'}, Severity ={1,2}, Status ={'Closed','In Progress','Pending For User Verification'}, Root_Cause ={'Env'}>}Duration) /118.5

Re: Master calendar and Fact table dates

Anonymous — Thu, 18 Jun 2015 12:00:25 GMT

Also sharing the dates table for Release dates,xls

Release	Environment	New_Environment	Release Start Date	Release End Date
R39	UTE1	UTE1	24-Feb-14	14-Jun-14
R39	#UTE1	UTE1	24-Feb-14	14-Jun-14
R40	UTE2	UTE2	26-May-14	13-Sep-14
R40	#UTE2	UTE2	26-May-14	13-Sep-14
R41	UTE1	UTE1	25-Aug-14	6-Dec-14
R41	#UTE1	UTE1	25-Aug-14	6-Dec-14
R42	UTE2	UTE2	17-Nov-14	14-Mar-15
R42	#UTE2	UTE2	17-Nov-14	14-Mar-15
R43	UTE1	UTE1	12-Jan-15	13-Jun-15
R43	#UTE1	UTE1	12-Jan-15	13-Jun-15
R44	UTE2	UTE2	20-Apr-15	19-Sep-15
R44	#UTE2	UTE2	20-Apr-15	19-Sep-15
R39	ALL	ALL	24-Feb-14	14-Jun-14
R40	ALL	ALL	26-May-14	13-Sep-14
R41	ALL	ALL	25-Aug-14	6-Dec-14
R42	ALL	ALL	17-Nov-14	14-Mar-15
R43	ALL	ALL	12-Jan-15	13-Jun-15
R44	ALL	ALL	20-Apr-15	19-Sep-15
R39	IDE1	IDE1	24-Feb-14	14-Jun-14
R39	IDE1	#IDE1	24-Feb-14	14-Jun-14
R40	IDE2	IDE2	26-May-14	13-Sep-14
R40	IDE2	#IDE2	26-May-14	13-Sep-14
R41	IDE1	IDE1	25-Aug-14	6-Dec-14
R41	#IDE1	IDE1	25-Aug-14	6-Dec-14
R42	IDE2	IDE2	17-Nov-14	14-Mar-15
R42	#IDE2	IDE2	17-Nov-14	14-Mar-15
R43	IDE1	IDE1	12-Jan-15	13-Jun-15
R43	#IDE1	IDE1	12-Jan-15	13-Jun-15
R44	IDE2	IDE2	20-Apr-15	19-Sep-15
R44	#IDE2	IDE2	20-Apr-15	19-Sep-15

Re: Master calendar and Fact table dates

Anonymous — Tue, 23 Jun 2015 06:04:42 GMT

I have tried to cut the timestamp out from the date function and it has worked.