5 Replies Latest reply on May 20, 2016 12:17 PM by Sarah Arnio

    Total repeat customers, multiple merchants with FIXED calc

    Sarah Arnio

      Hello,

      Hoping I can get some help with the following data that I’m trying to pull: I want to know the number of our customers who have purchased in more than 1 merchant type within the same app (app is represented by application_code and merchant type is represented by product_description in my DB).

       

      I went about this thinking I could create a field to represent those buyers who have more than 1 product_description and then filter by the app and drag the new field to the rows shelf. I created the [repeat cust (other merch)] field as follows:

      if { FIXED [buyer_email]:COUNTD([product_description])>1} then [buyer_email]

      END

      (I’m not too familiar with the FIXED calc, not sure if I’m using properly..)

       

      The problem is I’m getting users who have purchased in more than 1 merchant type across more than 1 app (it’s not limiting it to the app I want, even though I filtered this in the filter shelf). I'm wondering if there a way I can filter by the application_code within the [repeat cust (other merch)] field...?

       

      If it helps, the sql query would look like this: SELECT buyer_email, count(distinct product_description) from transaction where application_code = 'PLAZEZ-CO' group by buyer_email having count(distinct product_description) > 1

       

      I’m attaching the workbook here, the Repeats in other merchants tab shows the user detail so I can validate that the calculations were correct (preferably this would be a count of users on a monthly basis in a bar graph format), and the buyer_email ex I used to prove that the calculation is giving me multiple application codes.

       

      thanks in advance for the help!