count if set analysis greater than in straight table

[seWork]

Hi all,

I'm trying to count what is the frequency of the line value per item (Avg. and 98% of the lines)

My expectation is :

Item	days sold	Orders	Lines	Line value	Avg. Line value	Frequency(Count - Line value above avg.)	98% lines value	Frequency(Count - Line value above 98%)
1	134	297	306	936	3.06	45	37	3
2	218	2860	3094	631	0.2	174	4	53
3	216	2024	2206	458	0.21	70	4.54	43
4	116	746	754	2363	3.14	139	20	11

I tried to use

Count({<Line_Value={">=$(Fractile(aggr(Sum({<Line_Type={1,2}>} Line_Value),[Order Number]),0.98))"}>} DISTINCT [Order Number])

but the function calculates the "Fractile(aggr(Sum({<Line_Type={1,2}>} Line_Value),[Order Number]),0.98)" as 0

so i'm getting the counting of all of the [Order Number]

but when I'm using similar calculation for the average I'm getting the correct answer

Count({<Line_Value= {">=$(=(Sum({<Line_Type={1,2}>} Line_Value)/COUNT([Order Number])))"}>} DISTINCT [Order Number])

what is the problem and how could I fix it?

[Gysbert_Wassenaar]

The problem is that the set is calculated at the chart level, but the dollar expansion with your fractile function is using the dimension values of the rows. That means that it can't return anything other than 0 at the chart level, where there are no row level dimension values. By putting an = character inside the dollar expansion the expression will be calculated at the document level and can return a result. Your second expression does include the = character inside the dollar expansion and that does give a result. So try: Count({<Line_Value={">=$(=Fractile(aggr(Sum({<Line_Type={1,2}>} Line_Value),[Order Number]),0.98))"}>} DISTINCT [Order Number])