Decile for two variables

Analytics
Enthusiast

Decile for two variables

I want to divide the sales for each store into deciles and then I want to get the sales amount for the bottom of each decile. However when I do:

select store_nm, sales, QUANTILE(10, store_nm, sales) as DEC_SALES from store_detail;

the results divide ALL sales into deciles regardless of store_nm.

I was able to get the top and bottom sales amounts by store using the RANK function, but now I can't figure out a way to get the bottom boundaries for deciles within each store.

Any suggestions?

TIA,
Nolan
1 REPLY
Enthusiast

Re: Decile for two variables

For the bottom boundaries for deciles within each store, try using an ascending rank (RANK(Column ASC) as RANKED)and then GROUP BY Store_Column. Your lower deciles will be ranked highest, so you can then tack on a qualify statement(QUALIFY RANKED < 4). The example I gave would bring back the worst three deciles for every distinct Store_Column value.