5 Replies Latest reply on Feb 15, 2016 6:32 AM by Adam Crahen

# Value of top 5 customer sales in latest quarter

Hi

I have created a worksheet showing the sales in the latest quarter as a discrete pill and a spark line trend to go with it. What I would like to also do is create another discrete pill showing the top 5 customers sales. Latest quarter sales is expressed as - WINDOW_SUM(IF Last()=0 then SUM([Sales]) End)

Latest quarter - £280,594

Top 5 within quarter should be £44,283

Can anybody help?

Thanks

Lloyd

• ###### 1. Re: Value of top 5 customer sales in latest quarter

Hi Lloyd-

The Top 5 in an LOD function is not possible.  Here is an in-depth thread with more info.  There are also some links there to vote for ideas.

Rank using Level of Detail calculation in v 9.0

My workaround was to create the Latest Qtr Sales and Top 5 Customer Sales in a separate worksheet.

Latest Quarter

{fixed : Max(DATETRUNC('quarter',[Order Date]))}

Latest Qtr Sales

{fixed : SUM(IF [Order Date (Quarters)] = [Latest Quarter] THEN [Sales] END)}

Latest Qtr Customer Sales

{fixed [Customer Name] : SUM(IF [Order Date (Quarters)] = [Latest Quarter] THEN [Sales] END)}

Then I created a Set on the top 5 customers and did an extract.

I put the Set on Rows and then hid "Out".  The Set pulls the Top 5 Customer Sales and the other LOD pulls in the Latest Qtr sales.

I put both of these in a dashboard.  I hope this helps.

9.0 workbook attached.

• ###### 2. Re: Value of top 5 customer sales in latest quarter

1) Create a calculated field  as below to get the current QTR sales volume.

Name: Current QTR Sales

Syntax: if year ([Order Date] ) = year (today() ) and datepart('quarter', ([Order Date] ) = datepart('quarter', today() ) then [Sales] end

2) Right click on "Customer Name"  click on "Create a set " then select "Top" option

then selec the field "Current QTR Sales" then select Sum, 5 to create a set.

3)Drag the [Customer Name][Current QTR Sales]  on to work area, drag the Set on to filters shelf.

4) Now view will show Top 5 Customers as per Current QTR Sales Volume.

Best Regards

Kumar

• ###### 3. Re: Value of top 5 customer sales in latest quarter

Thanks guys

• ###### 4. Re: Value of top 5 customer sales in latest quarter

As an extension to this I need the sparkline to be based on the top 5 customer sales in each period. Is this possible? The top 5 sets are static so would perhaps need to use index ?

• ###### 5. Re: Value of top 5 customer sales in latest quarter

You can do a set on the sales field and then right click on your date filter -> Add to context.  Now your set will update when you change the date.