Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sabmaldun
Contributor II
Contributor II

build quartiles per month


Hello all,

I hope anyone can help me.

I have to build quartiles based on the sum(Partsales) for each Dealer per month and market.


My table looks like this
Year,
Month,
Partsales,
DealerID,
market,
Rank

I have already build ranks for the dealer per month and market based on Partsales, because I thought it helped for calculating the quartiles.
something like this

If rank <=0.4*total rankcount of that month then quartile1,

If rank >=0.4*total rankcount of that month then quartile2

Does anyone know how the exact script looks like for the solution. I cannot achieve it.
Thank you.

Kind regards

Labels (2)
3 Replies
jaibau1993
Partner - Creator III
Partner - Creator III

Hi!

Try something like the following expresison (I suposed that you have a table with dimensions Dealer, Market, Year and Month

aggr(
	ceil(
4 * rank(sum(Partsales)) / count(TOTAL <Year, Month, Market> DISTINCT Dealer) ) ,Year ,Month
,Dealer
,Market )

Try to understand how this expression works and adapt it to your needs!

Bests,

Jaime.

sabmaldun
Contributor II
Contributor II
Author

Hey,

thx for your Reply.

I have to do it in the load script, because based on the quartiles I have to build the average later on.

Anyone has a Solution for how to do it in the  LOAD SCRIPT?

 

Thank you,

Kind regards

 

sunny_talwar

Would you be able to provide some sample data and the output you expect to see from the sample data shared?