1 Reply Latest reply on Dec 1, 2018 6:20 PM by Fabian König

    Time constraints in join or blend

    Fabian König

      Hi there!

       

      Let's say I have 3 tables:

      - invoices

      - customers

      - customer groups

       

      plus a m-to-n relationship table called "LINK", which captures

      - which customer

      - belongs to which group

      - from which date

      - until which date (or NULL if there is no end date)

       

      I now want to have a revenue break-down based on the customer groups to which the invoiced customer was assigned at the time when the invoice was created.

       

      e.g.

       

      Invoice NumberInvoice DateInvoiced Customer IDRevenue
      1001Jan 1stJim100
      1002Feb 2ndTom500
      1003March 3rdJim50
      1004March 15thTom

      200

      1005April 4thJim75

       

       

      LINK IdCustomerGroupFromUntil
      1JimAJan 1stFeb 28th
      2JimBMarch 1stNULL
      3TomAMarch 1stNULL

       

      So we have a couple challenges:

       

      • Jim belongs to group A when Invoice 1001 is issued.
      • Jim belongs to group B when invoices 1003 and 1005 are issued.
      • Tom doesn't belong to any group yet when his invoice 1002 was issued.
      • Tom belongs to Group A when his invoice 1004 is issued.

       

      Ultimately I would like to see a crosstab as follow:

       

      GroupCustomerJanFebMarAprTotal
      AJim100100
      Tom200200
      BJim5075125
      (none)Tom500500
      Total10050025075925

       

       

      Formatting the crosstab is not so much the issue, but how can I get the Group assignments for Jim and Tom based on the invoice date ?

       

      Would I rather JOIN or BLEND this (since the other tables which are also needed in real-life make things quite complex already and double counting is an issue, I'd rather BLEND than JOIN).

       

      How would I go about linking the blended data based on the date constraint ?

       

      Thank you & Best Regards

      Fabian

        • 1. Re: Time constraints in join or blend
          Fabian König

          So I got a step further, thanks also to Sofya!

           

          Data prep:

          • create Left Joins:

            [invoices] LEFT JOIN [customers] LEFT JOIN [link] LEFT JOIN [customer groups]

          • create a calculation:

            [ValidAssignment] = [Invoice Date] >= [From] AND IF [Until] <> null THEN [Invoice Date] < [From] ELSE true END

           

          Use the [ValidAssignment] calculated field as a filter

           

          And there you go: group associations are linked to invoices based on when the groups have been assigned and the invoice date.