1 Reply Latest reply: Jun 29, 2010 9:31 PM by John Witherspoon RSS

    How to make the right data connections?

    TjeerdPolstra

      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?
          John Witherspoon

          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.)