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

[Oleg_Troyansky]

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

[vincent_ardiet]

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.

[vincent_ardiet]

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

[Oleg_Troyansky · Accepted solution]

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?

[hanna_choi]

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

[vincent_ardiet]

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.

[vincent_ardiet]

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

[hanna_choi]

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.

[vincent_ardiet]

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.