Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hi Sanjyot,
What exactly is wrong with the script you are using?
Hi Sanjyot,
Can you please elobrate?
thanks,
Rajesh Vaswani
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;
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:
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;
what type of chart are you using?
Have you unchecked Suppress Zero values on the presentation tab?
Hi,
I think is better if you post a test QVW file, is better for us to help you.
Regards.
I have unchecked Suppress Zero values. Using a bar chart.
!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.
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.