5 Replies Latest reply on Feb 26, 2018 1:55 AM by Antonio S

    Workaround to get a dimension with INCLUDE LOD expression

    maria.hahnloser

      I am trying to figure out if there is a way to generate a dimension while using INCLUDE expression in the calculation.

      (Note: I have read How to use an Include LOD calculation as a dimension, it poses indeed a similar question, but the proposed workaround using filtering doesn't quite work for me)

       

      I have a transaction dataset along the lines

          

      client iddatetransaction type
      105.07.2017A
      125.07.2017A
      122.08.2017B
      209.07.2017B
      315.07.2017A
      303.08.2017A

       

      I would like to classify my clients based on the set of transactions they performed, so as to later group them and produce statistics for each group
      e.g.

      client 1: client type = AB

      client 2: client type = B

      client 3: client type = A

       

      When considering the entirety of clients' transactions, I can do this using FIXED LOD expression, e.g.

      IF  {FIXED [client id]: COUNTD(IF [transaction type]='A' then [date] ELSE NULL end)} > 0

           AND {FIXED [client id]: COUNTD(IF [transaction type]='B' then [date] ELSE NULL end)} > 0

           THEN 'AB'

      ELSEIF ...

      The resulting calculation is a dimension and I can use it in my reports, but up to a point. I run into a slight problem when I include Date in my reports, e.g. to split stats per month. The computed client type is globally "fixed" and doesn't depend on transactions in a given month. Because I want to be able to compare the distribution of client types from month to month, setting context filter doesn't work for me.

       

      A logical way to accomplish this would be to change FIXED to INCLUDE to allow the date dimension to be automatically pulled into the calculation, but that generates a measure instead of a dimension. (Yes, I am aware that what I am trying to calculate is not exactly a classical dimension, since it depends on level of aggregation, but it is definitely not a measure either).  I can think of a workaround whereby I create a separate monthly-level calculation that includes MONTH(date) in LOD, e.g.  FIXED [client id],  MONTH(date) but this is somewhat unsatisfactory as I would need to define a separate calculation for each hierarchy level of date (week, month, quarter, year..) Is there a more elegant solution?