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;

[jpenuliar]

Hi Sanjyot,

What exactly is wrong with the script you are using?

[rajeshvaswani77]

Hi Sanjyot,

Can you please elobrate?

thanks,

Rajesh Vaswani

[Anonymous]

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;

[Anonymous]

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;

[tracysmart]

what type of chart are you using?

Have you unchecked Suppress Zero values on the presentation tab?

[Anonymous]

Hi,

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

Regards.

[Anonymous]

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

[Anonymous]

!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.

[Anonymous]

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.