Aggr with range sum

[Chinedu_CV]

Hi guys,

am trying to calculate rangesum of all amounts tilldate. 

I am using this 

Money(sum(aggr(RangeSum(Above(sum({<Schema = {'1.) Forderungen und sonstige Vermögensgegenstände'}>}Betrag_kennzahl),0,RowNo())),Schema,Konto,Mandant,date)))

Things work properly in my pivot table, but the moment I select a particulare month in the date filter, the result shown becomes incorrect. How can I get my formular to prevent this? 

[Benoit_C · Accepted solution]

Hello @Chinedu_CV,

It sounds like you want to calculate the RangeSum of all amounts up to the current date, regardless of any selections made in the date filter. 

You could try to use set analysis to ignore selections in the date field within your expression. 

For example:

Money(sum(aggr(RangeSum(Above(sum({<Schema = {‘1.) Forderungen und sonstige Vermögensgegenstände’}, date=>}Betrag_kennzahl),0,RowNo())),Schema,Konto,Mandant,date)))
 

In this expression, I added date=> within the set analysis to ignore any selections made in the date field. This way, the RangeSum calculation will consider all dates, not just the selected ones.

Regards,

Benoit

[Chinedu_CV]

Hey Benoit,

thanks man, it works. I had to uncheck all filters and then did the change in the set analysis. Otherwise, it doesnt work. 

Another small problem, it seems the aggr function hier counts the figures twice and returns a wrong sum, if i have more than entry in a month (see Screenshot). Is there a way to correct this? 

[Chinedu_CV]

This example in the screenshot should return 1479 and not 6463

[Benoit_C]

Hi @Chinedu_CV,

You are very welcome, yes, it’s important to make sure that all filters are unchecked before making changes to the set analysis expression.

For your other issue I advise you to create a separate topic in order to not mix issues and so that other users who face similar issue can find it easily.

Regards,

Benoit