topic Re: AGGR - Trying to sort on 2 dimensions with an expression in App Development

AGGR - Trying to sort on 2 dimensions with an expression

vincent_ardiet — Fri, 17 Oct 2025 13:54:16 GMT

Hi,
I'm going crazy trying to solve something I have some doubt it's even possible, it's why I'm requesting the help of the community.

I have built an example which is not exactly what I need to do but to illustrate, so don't ask why I want to do this :-).
I want to accumulate an amount ([Amount]) per level ([Level]) across the time but with the dates ([Date]) sorted to show the amounts by ascending order (for each level).

I manage to sort:

by date:
- Aggr( RangeSum( Above( Sum([Amount]), 0, RowNo())), [Level], [Date] )
by descending date (with an expression just to check that the expression usage works):
- Aggr( RangeSum( Above( Sum([Amount]), 0, RowNo())), [Level], ([Date],(=Num([Date]),DESC)) )
by amount but with total amounts per date (and not by level and by date):
- Aggr( RangeSum( Above( Sum([Amount]), 0, RowNo())), [Level], ([Date],(=Sum([Amount]),ASC)) )

I tried with another Aggr in the sort expression but it's not working:
Aggr( RangeSum( Above( Sum([Amount]), 0, RowNo())), [Level], ([Date], (=Aggr( Sum([Amount]), [Level], [Date]), ASC)) )

Do you have an idea? As someone already been facing this kind of request?

Here is what I'm expecting as a result:

My test script is:

Test:
Load [Level],Date([Date]) as [Date],[Amount] Inline
[Level,Date,Amount
Value1,45894,0
Value1,45895,115
Value1,45896,791
Value1,45897,-17
Value1,45898,-2938
Value1,45901,276
Value1,45902,2819
Value2,45894,0
Value2,45895,67834
Value2,45896,41164
Value2,45897,32626
Value2,45898,-30596
Value2,45901,-54394
Value2,45902,30127
Value3,45894,0
Value3,45895,2893
Value3,45896,-1218
Value3,45897,-1141
Value3,45898,2574
Value3,45901,-2131
Value3,45902,3593
];

Re: AGGR - Trying to sort on 2 dimensions with an expression

Oleg_Troyansky — Fri, 17 Oct 2025 14:36:32 GMT

Hi there!

I'd suggest to consider a couple of ideas:

1. The AGGR returns an array of values by Level and Date. If you wanted to see a single value, perhaps you need to enclose it into another aggregation function - Sum(), Min(), Max(), Avg(), etc...

2. Using ABOVE() with two dimensions is tricky - it's confined within the boundaries of the first dimension values, unless you add the TOTAL qualifier. I believe it should work as expected if you add TOTAL to the ABOVE() function.

Cheers,

Oleg Troyansky

Re: AGGR - Trying to sort on 2 dimensions with an expression

vincent_ardiet — Fri, 17 Oct 2025 15:13:01 GMT

It's working for the other sorts.
My problem is I want the sort order of [Date] being different for Level1, Level2 and Level3.
See here, by descending dates with, there is the expected reset on each level and computing as expected:
Aggr( RangeSum( Above( Sum([Amount]), 0, RowNo())), [Level], ([Date],(=Num([Date]),DESC)) )

If I'm sorting the date with the amount, it's following the global order, not the one by level:
Aggr( RangeSum( Above( Sum([Amount]), 0, RowNo())), [Level], ([Date],(=Sum([Amount]),ASC)) )

If you see the screenshot of what I'm expecting, the sort order of dates are not the same for each level.

Re: AGGR - Trying to sort on 2 dimensions with an expression

vincent_ardiet — Fri, 17 Oct 2025 15:52:01 GMT

I'm wondering if it's possible in fact.
Because, as you said, an Aggr is like creating a virtual table.
And, in a table, you cannot do this if I'm not wrong.
If you change the sort order of [Date], it cannot take in account [Level].
In this case, we need to replace the dimension [Date] with something like:
=Dual([Date],Aggr(Sum([Amount]),[Level],[Date]))

And, we cannot use an expression in an Aggr like something like this:
Sum(Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), [Level], (=Dual(Date,Aggr(Sum(Amount),Level,Date)),ASC)))

Re: AGGR - Trying to sort on 2 dimensions with an expression

Oleg_Troyansky — Sat, 18 Oct 2025 12:25:44 GMT

Perhaps it could work if you created a combined field Level-Date, and sort that field b by amount as the second dimension of the AGGR, keeping Level as the first dimension?

Re: AGGR - Trying to sort on 2 dimensions with an expression

hanna_choi — Mon, 20 Oct 2025 05:28:37 GMT

Hello @vincent_ardiet

Do you want it to be like this?

**** Expression

Sum(Aggr( Rangesum(Above(Sum(Amount),0,RowNo())), (Level, (Numeric, Ascending)), (Amount, (Numeric, Ascending)) ))

Re: AGGR - Trying to sort on 2 dimensions with an expression

vincent_ardiet — Mon, 20 Oct 2025 07:08:05 GMT

Yes @Oleg_Troyansky, I was also coming to the same conclusion.
I need to validate the cost in term of time and volume, for my specific needs I will need to create about 250 millions new values. But it seems to be the easiest solution.

Re: AGGR - Trying to sort on 2 dimensions with an expression

vincent_ardiet — Mon, 20 Oct 2025 07:15:00 GMT

Thanks for checking.
Your example is working because, out of laziness, the dataset I provided is already an aggregation of amounts. So, sum([Amount]) and [Amount] are identical.
If you add for example those 2 lines in the inline, you will see that it's not working unfortunately:
Value1,45896,-20000
Value1,45896,20000

Re: AGGR - Trying to sort on 2 dimensions with an expression

hanna_choi — Mon, 20 Oct 2025 07:53:53 GMT

Hello @vincent_ardiet

It's the amount of the same date, so how about group by it in data load editor?

I applied Group by Level and Date in the Data Load Editor, visualization works normally.

Re: AGGR - Trying to sort on 2 dimensions with an expression

vincent_ardiet — Mon, 20 Oct 2025 08:23:00 GMT

I have simplified the model for the community. In reality it's more complex (I have other dimensions) and moreover, we have a drilldown hierarchy on what is equivalent to my [Level] field.
The aggregation should be done in the chart and cannot be done statically in the loading script.

Re: AGGR - Trying to sort on 2 dimensions with an expression

marcus_sommer — Mon, 20 Oct 2025 11:30:21 GMT

In some scenarios it's possible to combine n dimensions / expressions in a mathematical way to get an unique key-value. In your case it might go in an direction like:

(Level * 1000000000) + (Date * 10000) + sum(YourExpr)

Maybe it's an alternative approach to adjust the sorting.

Re: AGGR - Trying to sort on 2 dimensions with an expression

Oleg_Troyansky — Mon, 20 Oct 2025 12:47:54 GMT

With such a large dataset, I'd be careful. If the number of distinct values is in millions, this AGGR won't fly, unfortunately.

Re: AGGR - Trying to sort on 2 dimensions with an expression

Oleg_Troyansky — Mon, 20 Oct 2025 18:51:57 GMT

I thought I responded on the phone, but it didn't seem to register...

With millions of distinct values, this kind of AGGR most likely won't fly... You'll need to look for a different solution, or come clean with your user that this won't fly with a large dataset.

Cheers,

Oleg Troyansky

Re: AGGR - Trying to sort on 2 dimensions with an expression

vincent_ardiet — Tue, 21 Oct 2025 11:03:00 GMT

Using a composite field is working.
With something around 30% of my final scope, I have created a new table with 2 million rows and 11 columns.
I've not implemented all the KPI needed however, for the time being, performances are not so bad (specially with an AGGR, an ABOVE and a IF in the expression - I need to compute a linear regression between 2 simulations sorted by an amount when the cumulate weight of their date is going above 5%).
Previously I was doing this computation using chart scripting, it works but it's slow and the constraints in term of how the results are presented are important (color coding, number format, totals in a separate object...).