topic Re: Need Help Quickly in QlikView

Need Help Quickly

Sat, 30 Jan 2016 03:59:26 GMT

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

Re: Need Help Quickly

MarcoWedel — Sat, 30 Jan 2016 21:19:39 GMT

Search for canonical Date calendar.

Regards

Marco

Re: Need Help Quickly

ziadm — Sun, 31 Jan 2016 01:15:12 GMT

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

Re: Need Help Quickly

MarcoWedel — Sun, 31 Jan 2016 13:57:58 GMT

‌Canonical Date

regards

Marco

Re: Need Help Quickly

MarcoWedel — Sun, 31 Jan 2016 21:19:43 GMT

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