Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to create an app that shows # tickets opened and # tickets closed on a given date. I have a single Ticket table that lists every ticket, including its Submit Date and (if it's Closed) a Resolve Date. I also have a Date Dimension table. At its simplest, I would need to show (e.g.)
'On 12/10/2018, Submitted: 300 and Closed: 320 tickets', which would then be part of a larger line chart.
I've tried creating a duplicate table, then using the DateDim table to join a date to the Submit Date of one, and to the Resolve Date pf the second table, but I'm getting a 'Duplicate Derived Field' error.:
Duplicate Derived Field: DERIVE FIELDS FROM FIELDS [DateValue-SubmissionDate], [SubmissionTS], [ModifiedTS], [SLAResponseTS], [SLAResponseDueTS], [SLAResolutionTS], [ResolutionDate], [SLAResolutionDueTS], [ETLLDDTM], [DateKey], [DisplayString], [LastFridayDate], [MonthEndDate], [TomorrowDate], [ClosedMasterTicketList.SubmissionTS], [ClosedMasterTicketList.DateValue-SubmissionDate], [ClosedMasterTicketList.ModifiedTS], [ClosedMasterTicketList.SLAResponseTS], [ClosedMasterTicketList.SLAResponseDueTS], [ClosedMasterTicketList.SLAResolutionTS], [ClosedMasterTicketList.SLAResolutionDueTS], [ClosedMasterTicketList.ETLLDDTM] USING [autoCalendar]
There are dup field names, but they are qualified by the duplicate table's name (ClosedMasterTicketList). Any suggestions?
Thanks in advance.
You don't need to duplicate the data. Instead, link both SubmitDate and ResolveDate to a canonical date through a link table. Download the Qlik Sense version of this tutorial for an example:
Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
You don't need to duplicate the data. Instead, link both SubmitDate and ResolveDate to a canonical date through a link table. Download the Qlik Sense version of this tutorial for an example:
Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com
Yes, this will work for multiple dates in the same fact table. for the canonical (common) date, Load from the fact table multiple times, once for each date type.
DateLink:
LOAD
TicketId // Fact Key
,OpenDate as Date // Fact Date
,'Open' as DateType // Fact Type
RESIDENT Tickets;
LOAD
TicketId
,CloseDate as Date
,'Close' as DateType
RESIDENT Tickets
;
For the separate calendars, generate using the individual date field -- OpenDate or CloseDate.
-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com