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

# Chart with total amount of Sales for TOP 3

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

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

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

HI Jonathan,

thanks for your reply. I used the rank function already, but it throws back for each of the top 3 a single value. And this results in three lines in a line chart. I need only one single line with the cumulated sum of all top 3 customers.

Thanks,

Mat

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

I assume you don't have the Customers as a dimension of your chart ?

Try using a "ALL" addition within your expressions to override your chart's date dimension and return one set of the top 3 for the whole time period:

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

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

)

This example is total guesswork though I'm afraid.

Jonathan