topic Re: Different dates over the same dimension in QlikView

Different dates over the same dimension

dahpet03 — Wed, 15 Mar 2017 21:16:41 GMT

In my data model there are two different types of cases (actually there's more but that's not really relevant right now). We can call them incidents and change requests. There are times when these different cases are connected to eachother and this connection is represented in the data model.

What I would like to do is to have a line chart in which I plot incidents over their reported date and then have a second line in the same chart for the connected change requests. The problem is that the change requests uses a different date dimension, let's call it the submit date. So, my question is if there's a way to do this without making changes to the data model. I have a canonical date in the data model that would obviously work but that compromises the users ability to make selections to the relevant dates more than I would like.

I've tried everything I can think of when it comes to set analysis but I'm starting to think that's it's simply not possible to achieve without resorting to the use of a canonical date or some other redesign in the data model.

Does anyone here have some clever technique that might do the trick? Did I even manage to explain my problem so you can understand it? I'm afraid I'm not in a position to give you any sample data at the moment but all help is appreciated, even if it's just to confirm that it indeed is impossible.

Re: Different dates over the same dimension

marcus_sommer — Thu, 16 Mar 2017 08:57:26 GMT

I think I would try to use the canonical date as dimension and connecting it within the expression in a set analysis with other selections, I mean for example something like:

sum({< CanonicalDate = p(Date) >} Value)

whereby it will be probably more complex with something like: p({< Field1 = p(Field2)>} Date) and you might need further to add a modificator or operator to the various parts.

If this in the end will be more easier and understandable I'm not sure - maybe it's more suitable to prevent false selections by using this in a separate sheet with only appropriate selection-fields and/or alternate states and/or using selection-actions by activate/deactivate this sheet or something similar.

- Marcus

Re: Different dates over the same dimension

mfchmielowski — Thu, 16 Mar 2017 09:03:01 GMT

Hey.

I dont think that you'll be able to achive a chart where time is dimention without canonical calendar or other data model modifications.

I've had similar problem during preparation of report from atlassian jira app. The solution was similar to below sample:

incidents:
load * inline [
     "incidentID", "incidentKey", "incidentCreateDate"
     1, "key-1", "2017-01-01"
     2, "key-2", "2017-01-02"
     3, "key-3", "2017-01-03"
     4, "key-4", "2017-01-03"
     5, "key-5", "2017-01-03"
     6, "key-6", "2017-01-06"
];

Concatenate(incidents)
load * inline [
     "crID", "incidentID", "crCreateDate"
     1, 1, "2017-01-02"
     2, 1, "2017-01-03"
     3, 2, "2017-01-04"
     4, 1, "2017-01-04"
];


cal:
load
     min(incidentCreateDate) as minIncidentCreateDate,
     max(incidentCreateDate) as maxIncidentCreateDate
     , max(crCreateDate) as maxCrCreateDate
Resident
     incidents
;

let vMinDate = peek('minIncidentCreateDate', 0,'cal');
let vMaxDateInc = peek('maxIncidentCreateDate', '0', 'cal');
let vMaxDateCr = peek('maxCrCreateDate', '0', 'cal');

let vMaxDate = if($(vMaxDateInc) > $(vMaxDateCr), $(vMaxDateInc), $(vMaxDateCr));

drop table cal;

let vDiff = $(vMaxDate) - $(vMinDate);
     
for i=0 to '$(vDiff)'
     GlobalCalendar:
     load
          date($(vMinDate)+$(i), 'YYYY-MM-DD') as globalCalendarDate
     AutoGenerate(1);
next i;


NoConcatenate
incidentsTmp:
load
     *
     , if(not isNull(crCreateDate), date(crCreateDate, 'YYYY-MM-DD'), date(incidentCreateDate)) as globalCreateDate
Resident incidents;

drop table incidents;

Re: Different dates over the same dimension

dahpet03 — Fri, 17 Mar 2017 06:28:14 GMT

Thanks for your replies, it seems to confirm my own findings that it's not really possible to do it the way I would want. I'm looking into ways to change my data model or simple a different frontend design to present the data.