Master calendar and Fact table dates

[Anonymous]

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;

[kuczynska]

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.

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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