How to make the right data connections?

Tue, 29 Jun 2010 13:57:57 GMT

Hi all,

I am somewhat puzzled by the following:

Imagine an excel sheet with in
column1: customerID
column2: contractID
column3: acceptance date of contract

A customer is linked to more than 1 contract.

From this data (which is part of the whole sheet) I am creating a line chart, which:
plots the first contract date (from any 1 customer) on the x-axis, to the average number of contracts per customer. So I need to count the distinct number of contracts per customer ID, and set it off against the grouped earliest (2002, 2003, 2004 etcetera) contract dates. My problem is that I do not know how to find the first contract date per customer and use that in the dimension label, I suppose I should take action on the during load...? Is my problem clear?

Any thoughts would be great!

Regards

How to make the right data connections?

johnw — Wed, 30 Jun 2010 01:31:09 GMT

So, you kind of want Customer as a dimension, but mapped to the first contract date for the customer on the X axis?

I think maybe this?

dimension = aggr(dual(only(CustomerID),min("acceptance date of contract")),CustomerID)
expression = count(distinct contractID)

Probably need to make the X axis continuous as well. And yeah, if it DOES work, probably better to do most of the work during the script, and just use the new field as the dimension, something like this:

LEFT JOIN ([Contracts])
LOAD
customerID
,date(min("acceptance date of contract")) as FirstContractDate
RESIDENT [Contracts]
GROUP BY customerID
;
LEFT JOIN ([Contracts])
LOAD *
,dual(customerID,FirstContractDate) as CustomerAndFirstContractDate
RESIDENT [Contracts]
;

dimension = CustomerAndFirstContractDate
expression = count(distinct contractID)

But I may have misunderstood completely. (Or may have understood, and have posted a totally unworkable solution.)

topic How to make the right data connections? in QlikView

How to make the right data connections?

How to make the right data connections?