Top N customers in Current Quarter, Last 4 Quarters and PY

[Kris1]

Hi All,

Thanks for your support in advance.

I created a stacked bar chart, Quarter_Year, Customer as Dimension and Sum of sales as Measure. As shown below.

Now i want to see Top 3 Customers of Current Quarter, Top 3 Customer of last 4 Quarters Like this Top 5 and Top 10 customers using drop down selection.

Could you please help me how to do this one.

Regards,

Krish

[edwin]

there are a couple of components to this:

1. identify the quarters, one way is determine what quarters you are interested in and store that in a variable which you can use in  your set analysis. 
current quarter: vQuarter=    =only(quarterRank)  - this assumes your user selects a month and this returns the current quarter

2. save your top N number in a variable which you will also use in your set analysis
vTopN = 3  (you can either use a slider, or a variable selector)

3. the expression itself.  

Sum({<monthDate=, quarterRank={$(vQuarter)}, 
customer={"=rank(Sum({<monthDate=, quarterRank={$(vQuarter)}>}amount))<=$(vTopN)"}
>}amount)

the first line says: ignore the selected month; select all records with quarterRank =currently selected quarter
2nd line says select customers where rank <= 3 

this gives you the top 3 from current quarter.  if you want other quarters, you need to build a way for user to select the quarter and then in your vQuarter variable create an expression that returns the desired quarter or quarters.  for last x quarters you need to return the quarter ranks concatenated by commas: 8,7,6,5  and so on.  this just gets inserted into the set analysis

you can expand on that

try this:

data:
load monthDate,
'Q' & CEIL(NUM(MONTH(monthDate))/3) & ' ' & year(monthDate) as quarterYear, 
CEIL(NUM(MONTH(monthDate))/3) + year(monthDate)*10 as quarterCode, 
'C'&iterno() as customer, floor(rand()*100) as amount
while iterno()<11;
load addmonths(monthstart(today()),1-iterno()) as monthDate 
while iterno() <=24;
load 1 autogenerate(1);

tmpQuarters: 
load distinct quarterYear, quarterCode resident data;

noconcatenate quarters:
load quarterYear, rowno() as quarterRank resident tmpQuarters order by quarterCode;

drop table tmpQuarters;

 the table shows which ones are the top for the current quarter , the chart just shows the top 3