1 Reply Latest reply: Aug 29, 2016 9:17 AM by Daniel Balchasan RSS

    Issue with comparing rows on different tables

    Daniel Balchasan

      Hello,

       

      I'm working on some Set analysis to check performance of customers post-sale.

      My data model includes a table for Sale (and other) events, and a table for person check-ins. They are linked by a 'PERSON_ID' field.

       

      I made a measure to count the number of check-ins a person has on the first 21 days after a sale. I ended up with this set analisys statement:

      count({<Person_DateTime = {"=CHECK_IN_DATE>=DATE and CHECK_IN_DATE<=DATE+21"}, EVENT_TYPE={'SALE'}, DATE = {">$(vPreviousMonthEnd)"}>} distinct Person_DateTime)

       

      Date : Date field on my Events (Sales) table.

      CHECK_IN_DATE : Date field on my checkins table.

      Person_DateTime: Unique identifier of a checkin.

      let vPreviousMonthEnd = '=MonthEnd(AddMonths(max(DATE),-1))';

       

      The variable vPreviousMonthEnd is used to only count the checkins of sales in MTD

       

      This seems to work fine when a person has had only a single sale. It properly counts the number of checkins in the first 21 days after the sale.

      However, in cases where the person has had more than 1 sale, I'm getting no results, unless I limit the sales using a selection (E.g. on the DATE field).

       

      Can someone please explain this behavior?

      How could I change my measure to work with persons with multiple sales?

       

      Thanks in advance 

       

      Message was edited by: Daniel Balchasan Added a QVF with test data and the measure I'm using,