AGGR - Trying to sort on 2 dimensions with an expression

[vincent_ardiet]

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
];

[marcus_sommer]

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.

[Oleg_Troyansky]

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

[Oleg_Troyansky]

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

[vincent_ardiet]

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...).