Aggr with Above using if condition

[agigliotti]

Hello,
I'm in troubles with the below expression:

=sum(
aggr(
RangeSum(
above(
sum(
if(
Partite_CHIUSE.DataOperazione<%DataRegistrazione, 0, if( [Movimenti Scadenze.DataScadenzaSCA]<%DataRegistrazione, [Movimenti Scadenze.ImportoSCA], 0 ) )
)
, 0, RowNo(TOTAL) ) )
, %DataRegistrazione, %ChiaveScadenzaPagata )
)

in a straight table using %DataRegistrazione as dimension.

I need to compare the two fields Partite_CHIUSE.DataOperazione and [Movimenti Scadenze.DataScadenzaSCA] with %DataRegistrazione  of the current row  for all  above rows.

Is that possible?

Could anybody help me to achieve it? @sunny_talwar   @hic @stevedark @Gysbert_Wassenaar @rubenmarin @Thiago_Justen_ @Anil_Babu_Samineni @kaushiknsolanki @marcus_sommer @tresesco  

Many Thanks in advance for your time.

Best Regards and Take Care
Andrea

[Andrea_Bertazzo]

Hi Andrea,

Thanks for the question. What is the result of this formula? Do you get an error message?

It is difficult to test the formula without the data, but did you try to check each single part of it separately? it will be easier for you to find where it fails. For example, you could create a variable for the If() and verify that it shows the correct values. You can then use directly the variable in the formula.

[agigliotti]

Hi Andrea,

I'm pretty sure that the problem is due to the value of %DataRegistrazione field used to compare the two fields value.
I'd need to use the value of the current row even when processed inside the Above function for all previous rows.
I really hope now is more clear.
Thanks.

[Andrea_Bertazzo]

Hi Andrea,

It is difficult to troubleshoot this without the data. Check that the fields you are comparing are both in numeric format. It may be necessary that you convert them by using the num() funtion, e.g.: num(Partite_CHIUSE.DataOperazione)<num(%DataRegistrazione).

I would do step by step tests on the formula. What happens if you evaluate just the comparisons, for example by putting the expression in a Text object in the Sense app: Partite_CHIUSE.DataOperazione<%DataRegistrazione,

Are you getting the expected result?
Then you can proceed evaluating the rest of the formula step by step. This helps to understand where the problems are.

[agigliotti]

Hi Andrea,

I just attached a QVW document where you can see the actual and the expected result.

I hope it will be useful to understand what I'm trying to do.

Best Regards
Andrea

[Andrea_Bertazzo]

Hi Andrea,

I had a look at your QVW. Selecting the 30th of June as date, I see that the values of the fields are the following:

Partite_CHIUSE.DataOperazione=06/08/2021

%DataRegistrazione=30/0672021

[Movimenti Scadenze.DataScadenzaSCA]=10/09/2021

So, the if() give 0 as result as expected. Therefore, the Above() and the RangeSum() are also throwing 0 as result.

[agigliotti]

Hi Andrea,

In that table it's correctly showed the "Open Amount" over time.

What I'm trying to achieve is to get the "Expired Amount" that is part of the open amount.

ex. for 30th of June as date (with no selections) the expired amount should be about  of  15.289,04  (that is necessary less than open amount of 18.771,04 ).

The user should not make any selections within the table otherwise data does not make sense.

Best Regards
Andrea

[Digvijay_Singh]

May be create a test straight table with the separate expression for - 

sum(
if(
Partite_CHIUSE.DataOperazione<%DataRegistrazione, 0, if( [Movimenti Scadenze.DataScadenzaSCA]<%DataRegistrazione, [Movimenti Scadenze.ImportoSCA], 0 ) )
)

and then try to identify manually the final result and then compare with the results of final expression having above..

[tresesco]

@agigliotti , could you please explain your expression/logic (what you are trying to achieve) in words?

[agigliotti]

In few words I'm trying to show the amount of expired items over time.
To calculate it I need to process each item ( "Movimenti Scadenze" table) to check if its amount ( [Movimenti Scadenze.ImportoSCA] field )  is expired or not evaluating the below condition:
if(Partite_CHIUSE.DataOperazione<%DataRegistrazione, 0, if( [Movimenti Scadenze.DataScadenzaSCA]<%DataRegistrazione, [Movimenti Scadenze.ImportoSCA], 0 ) )

CGMOV03L :  accounting entry table

The "Expired Amount"  MUST BE  less or equal  to "Open Amount"  over time.

Payments are stored in "Partite_Chiuse" table,  where "Partite_CHIUSE.DataOperazione" field contains the payment date.

Items and Payments are linked with the field named "%ChiaveScadenzaPagata".

[Movimenti Scadenze.DataScadenzaSCA] : item expire date field

%DataRegistrazione : accounting entry date field

Please tell me if you need more informations.

Thanks