topic Re: build quartiles per month in QlikView

build quartiles per month

sabmaldun — Sat, 16 Nov 2024 21:20:56 GMT

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

Re: build quartiles per month

jaibau1993 — Tue, 26 Feb 2019 09:08:07 GMT

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.

Re: build quartiles per month

sabmaldun — Tue, 26 Feb 2019 13:52:22 GMT

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

sunny_talwar — Wed, 27 Feb 2019 02:03:08 GMT

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