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

Kris1 — Tue, 07 Dec 2021 02:48:07 GMT

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

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

edwin — Tue, 07 Dec 2021 20:53:33 GMT

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

topic Re: Top N customers in Current Quarter, Last 4 Quarters and PY in App Development

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

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