aggr formula for each line

[louisernould]

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

[marcus_sommer]

Maybe this is helpful: Re: Average of fractile

- Marcus

[Anil_Babu_Samineni]

Can I know the use of 0.1 value

[sunny_talwar]

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

[rwunderlich]

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

[louisernould · Accepted solution]

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