3 Replies Latest reply on Mar 31, 2016 12:42 PM by Sho Fujiwara

    Calculations with parameters

    Elías Santos

      Dear good morning , I have a question with a calculated field . I have a base with two columns : Years and customers. Must perform a calculation which brings me only " new customers " , using the following parameter : the customer is considered "new costumers" when it appears in 2016 and not in 2015. It is possible to do this ? Where should I start ?.


      i think in : When [years] = '2016' then costumers

      when [years] = '2015' then costumers.

      but now I can not think how to do to bring only "new cosumers"


      Thank you very much.

        • 1. Re: Calculations with parameters
          Sho Fujiwara

          Hi Elías!


          I would suggest that you post your workbook - or a very simplified example - so that we can see how your data is laid out and show you the exact solution.


          I think what you're looking for is a Level of Detail calculation, where for each customer, you calculate whether or not they show up in 2015, and if so, whether they show up in 2016.


          Call the field something like 'New_in_2016'

          where the calc is:

          {Fixed Customer:

          min(If Year = '2015'

          THEN 0

          ELSE 1



          How to interpret this calculation:

          For each member, look through all of their rows. If [Year] = 2015 for a row, give it a value of 0. If not, give it a value of 1. Taking the min of that 1) ensures that the LoD works (as it requires an aggregate) and 2) takes a value of 0 if the Member had at least one order in 2015.


          Assuming that your data only includes 2015 and 2016, then you can sum up all 'New_in_2016' to get all your new members in 2016.


          Hope that helps,



          1 of 1 people found this helpful
          • 2. Re: Calculations with parameters
            Elías Santos

            You are genius. thank you very much

            it works perfect, now i use New_in_2016 as a filter.

            how do you learn to do this? i do the advanced calculations training and don`t teach this.

            1 of 1 people found this helpful
            • 3. Re: Calculations with parameters
              Sho Fujiwara

              Great! Honestly, it was through reading through these forums and looking at similar examples. LoD calculations are a savior. I asked a questions a few months ago that showed me a similar solution with LoD calculations that I found very helpful.  Create Cohort Set based on Conditions of Two Measures


              Make sure you mark the question as Answered so other people can find the solution as well!

              1 of 1 people found this helpful