3 Replies Latest reply: Feb 6, 2013 4:34 AM by Jonathan Brough RSS

    Chart with total amount of Sales for TOP 3

    Mathias Mühlfelder

      Folks,

       

      I have searched the forum endlessly, but i couldn't find my solution.

       

      I am trying to create a line-chart, with only one line( grouped lines or bars i what I have achieved already). And this line shall include the total Sales Sum of the top 3 customers.

       

      Meaning: Chart shall have a X dimension which is date. The Y-dimension should be the total sum of Sales of the top 3 customers on this particular day.

       

      Columns in the table are:

      - MasterDate // Date of each day

      - SendingPartner // Customer Name

      - Sales // Amount of sales on this day

       

      Can you help me with building an expression which works in a chart. I am able to calculate the sum already for a text box, but drilling this down always gives me by using the aggr function also the SendingPartner split. And I'd like to avoid this, because i the chart i only need one line.

       

      Thanks so much,

      Cheers

        • Re: Chart with total amount of Sales for TOP 3
          Jonathan Brough

          Sounds like you'll need to use the rank() function as a condition within your expression.

          This would give you the rankings:

           

          rank(sum({$<TransDate={'>=$(SalesStart) <=$(SalesEnd)'}>} SalesValue))

           

          This could then probably be used as follows (not sure how to do exclusively with set analysis though):

           

          sum(if(rank(sum({$<TransDate={'>=$(SalesStart) <=$(SalesEnd)'}>} SalesValue))<=3,

          sum({$<TransDate={'>=$(SalesStart) <=$(SalesEnd)'}>} SalesValue)

          )

           

          Haven't tested this myself.

          Jonathan