topic Re: aggr formula for each line in QlikView

aggr formula for each line

louisernould — Wed, 25 Nov 2020 16:16:04 GMT

hi everybody,

I have a large table with x dimensions:

model, article, and Density.

Model	Article	Density
A	a1	1
A	a2	3
A	a3	5
A	a4	2
B	b5	1
B	b6	2

i'm looking for a formula which can tell me how many articles I have in each model, when the Density is under the 1st fractile (One fractile distinct by model).

so something like that:

Model	Nb of article	nb of article under the 1st fractile
A	4	1
B	2	0

i'm already trying with this formula: count({<Density={"<$(=fractile(aggr(Sum(Density),[Artcile]), 0.1))"}>}[Article])

But, the result returns the number of article when the density in under the 1st fractile for all the base, and not for each model:

Model	Nb of article	nb of article under the 1st fractile
A	4	1
B	2	1

if someone can help me ? you will be so nice !!!

Thank you

Regards

Louis

Re: aggr formula for each line

marcus_sommer — Tue, 04 Oct 2016 12:23:53 GMT

Maybe this is helpful: Re: Average of fractile

- Marcus

Re: aggr formula for each line

Anil_Babu_Samineni — Tue, 04 Oct 2016 12:47:13 GMT

Can I know the use of 0.1 value

Re: aggr formula for each line

sunny_talwar — Tue, 04 Oct 2016 12:49:15 GMT

Its for the fractile function specifying that the expression needs to find 10th percentile.

Re: aggr formula for each line

rwunderlich — Tue, 04 Oct 2016 16:10:39 GMT

The issue here is that your DSE $() expression will be evaluated only once for the entire chart, not per dimension. To evaluate on a Dimensional basis, you will have to change to using if().

sum(

aggr(

if(Density<

Fractile(TOTAL<Model>Density,0.1)

,1,0)

,Model,Article, Density)

)

BTW, I think "1" is the correct value when using the .1 fractile. See attached.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: aggr formula for each line

louisernould — Tue, 04 Oct 2016 16:18:36 GMT

Hi ! Thank marcus_sommer‌ for this link, it helped me

because I found it !!!!

the final formula is : Count(Aggr(If([Density]< Fractile(TOTAL <Model> Density,0.1), [Article]), [Model],[Article]))

I just saw rwunderlich‌'s answer, it's the same

thanks for your time guys !

Louis