4 Replies Latest reply on Jul 8, 2015 4:07 PM by Gianni Ceresa

    Current Customer Status with SCD Type 2 in OBIEE - measure

    2973433

      Hi experts,

       

      I have a fact table with SCD2 type that contains status of customer (let's say active, pending and inactive).

      I want to create a measure that shows me number of active customers in particular day (ie 2015-04-30).

       

      The table looks more or less like this:

      IDTime fromTime tostatus
      12015-01-012015-03-01initial
      22015-03-022015-04-01pending
      32015-04-012015-04-30active
      42015-05-012099-01-01closed

       

       

      Normally in SQL it would look like:

      select * from fct_customer

      join dim_date

      on d.gid between valid.from and valid.to

      where d.gid=20150429

      (sorry for possible mistakes but I hope it is more or less clear)

      and in this case it would show "active"

       

      Is there any way to do it in bi admin tool?