5 Replies Latest reply: Apr 27, 2015 4:24 AM by hariprasad avula RSS

    How to display a field that links to first record of an aggregate calculated dimension

    William Choo

      Hi All,

       

      I have a chart/graph where I currently display Sales (Y-axis) vs Customer (X-axis) using

       

      Expression: sum(aggr(count( distinct Customer ID),Customer ID)))

      Dimension: Customer

         

      SalesCustomerCustomer IDUnique ID
      $500Adam1001000
      $200Alyssa2001000
      $400Carl3002000
      $900Daniel4003000
      $100Andrew5001000
      $430Candice6002000
      $700Cory7002000
      $350Denise8003000

       

      A new business logic requires that I group customers with same Unique ID for displaying instead of Customer ID, so I need to show something like the 1st Customer in the Unique ID grouping instead of each Customer per Customer ID.


      I'm using aggr(Customer, Unique ID) for the new Dimension, but users obviously prefer to read text instead of a cryptic code.

      However, I'm having some difficulty trying to show this. In addition, I suppose I have to change Expression to sum(aggr(count( distinct Unique ID),Unique ID)))


      My expected output would be something like:

         

      SalesCustomerCustomer IDUnique ID
      $800Adam1001000
      $1,530Carl6002000
      $1,250Daniel4003000


      The data is calculated 'live' so a LOAD script with GROUP BY wouldn't do. I believe there should be a nice clean expression that would do the trick, though it's been eluding me Not sure if it's a combination of only() or aggr() or some cryptic function.....????


      What should I use for the new dimension in order to display Customer (based on Unique ID) for my requirement?


      Can anyone help ? Thanks in advance!