Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
sabmaldun
New 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
Contributor II

Re: build quartiles per month

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
New Contributor II

Re: build quartiles per month

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

 

Re: build quartiles per month

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