build quartiles per month

[sabmaldun]

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

[jaibau1993]

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]

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?