Need Help Quickly

[#1]

Hi All,

I am facing trouble to get the solution please help

I have 2 fields

one field is for created date,

second field is for resolved date,

please find the attachment.

i need to prepare chart that chart should display count(crateddate),count(resolveddate) based on the month

here the problem is i need to get the month from these two fields i am unable to do it please help me asap,

thanks and regards,

k.p

[MarcoWedel]

Search for canonical Date calendar.

Regards

Marco

[ziadm]

hi

you have to create a master calendar ranging from minimum date of whatever field (created or resolved) to maximum

dates of whatever date (created or resolved) . 

your fact table should  modeled and  linked to master calendar table with extra field to tag the record as creation or resolved. Your link should be the Date

store 'Created'  as MyField

Store 'Resolved' as MyField

you have to use set Analysis to aggregate as Resolved

you to submit example of your fact table in order to clarify it more

[MarcoWedel]

‌Canonical Date

regards

Marco

[MarcoWedel]

one example:

tabTickets:
LOAD TicketID,
     DayName([created timestamp]) as [created date],
     DayName([resolved timestamp]) as [resolved date],
     Time(Frac([created timestamp])) as [created time],
     Time(Frac([resolved timestamp])) as [resolved time];
LOAD RecNo() as TicketID,
     Timestamp(Cdate) as [created timestamp],
     Timestamp#(Ticket_ResolveDate,'DD/MM/YYYY hh:mm') as [resolved timestamp]
FROM [https://community.qlik.com/servlet/JiveServlet/download/964033-208690/sample.xls] (biff, embedded labels, table is Sheet1$);

tabDateLink:
CrossTable (dateTypeTemp, date)
LOAD TicketID,
     [created date],
     [resolved date]
Resident tabTickets;

Left Join (tabDateLink)
LOAD Distinct
     dateTypeTemp,
     SubField(dateTypeTemp,' ',1) as [Ticket state]
Resident tabDateLink;

DROP Field dateTypeTemp;

tabCalendar: 
LOAD *, 
     Day(date) as Day, 
     WeekDay(date) as WeekDay, 
     Week(date) as Week, 
     WeekName(date) as WeekName, 
     Month(date) as Month, 
     MonthName(date) as MonthName, 
     Dual('Q'&Ceil(Month(date)/3),Ceil(Month(date)/3)) as Quarter, 
     QuarterName(date) as QuarterName, 
     Year(date) as Year, 
     WeekYear(date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(date) as MinDate, 
     Max(date) as MaxDate 
Resident tabDateLink;

hope this helps

regards

Marco