1 Reply Latest reply on Oct 30, 2018 5:28 AM by Zhouyi Zhang

    Sum based on dynamic date and criteria (case function) error

    kristiina.truuverk

      Hi,

       

      I am trying to sum together values that meet several criteria:

      1) happened before a selected date <= is controlled with 2 parameters

      2) meet certain criteria of a dimension.

      3) are customer dependent

       

      Basically I am trying to calculate together the average lifetime of a customer until a certain date.

      In my database I have dimension [status] which indicates if a client is active or not on that specific day, I have the [client_id] and [date].

      I need to to make period selection for that calculation dynamic so I have created 3 parameters:

      1) [Date select] <= equals either to today or a specific date from which stakeholder wants to see the data

      2) [Period select] <= has values 'day', 'week', 'month', 'quarter'

      3) [Condition] <= any numeric value can be inserted.

       

      To calculate the average lifetime of a customer I have managed to come up with a formula (but this considers all time):

      { include [client_id]:sum(

      CASE [status]

      When "generating_income" then 1

      when "brand_new" then  1

      When "reactivated" then 1

      END)

      }

       

      But I would need now to add here that the formula needs to only sum together those values which are before the dynamically selected period For example sum together all values that are in the table but exclude last 2 weeks. In this case Date select= today, period select = week and condition = 2.

      I tried something like this, but it doesn't give me a correct value:

       

      if [date] < DATEADD([Period select],-[Condition],[Date select]) then

      { include [client_id]:sum(

      CASE [status]

      When "generating_income" then 1

      when "brand_new" then  1

      When "reactivated" then 1

      END)

      }

      END

       

      What am I doing wrong? The main aim is to understand if the average lifetime of a customer has increased within a certain time period.