Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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])