3 Replies Latest reply: Oct 12, 2016 9:31 AM by Alejandro Rouiller RSS

    help needed with metric

    Alejandro Rouiller

      Hi folks,

      First of all, I'm not fluent in english, sorry for that.

      Sencond, thanks in advance for any kind of help you can provide me.

       

      Here is the situation.

      I'm exploring some solutions to explote de data in a datamart and I have a problem with a metric.

       

      I'm trying to create a single metric to measure the quantity of clients.

      The behaviour of the metric should be  the next:

      When the level of analysis is "month" the metric should show the number of clients in that mont ( SUM(CLIENT_QUANTITY) )

      But, when the level of analysis is "year" the metric show show the number of clients in the

      last month's year that have data.

       

      The (partial) structure of the data mart is the next:

       

      Date dimmension

      SK_DATE

      DATE_STR (the representation of the date in dd/MM/yyyy format)

      DAY_OF_MONTH

      MONTH_NUMBER (with values from 1 to 12)

      YEAR

      MONT_YEAR (an integer value compossed by YEAR*100 + MONTH_NUMBER)

       

      Client dimmension

      SK_CLIENT

      CLIENT_NAME

      CLIENT_SUR_NAME

       

      The fact table:

      is a factless fact, and its granularity is one record per client at the first day of the mont

      SK_CLIENT

      SK_DATE

      CLIENT_QUANTITY (always 1)

       

      I look forward for your reply.

       

      Kind regards,