topic Re: Aggr with Above using if condition in QlikView

Aggr with Above using if condition

agigliotti — Thu, 07 Oct 2021 08:00:42 GMT

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 @tresB

Many Thanks in advance for your time.

Best Regards and Take Care
Andrea

Re: Aggr with Above using if condition

Andrea_Bertazzo — Mon, 04 Oct 2021 15:44:14 GMT

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.

Re: Aggr with Above using if condition

agigliotti — Mon, 04 Oct 2021 15:56:48 GMT

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.

Re: Aggr with Above using if condition

Andrea_Bertazzo — Tue, 05 Oct 2021 07:34:14 GMT

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.

Re: Aggr with Above using if condition

agigliotti — Tue, 05 Oct 2021 09:33:29 GMT

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

Re: Aggr with Above using if condition

Andrea_Bertazzo — Tue, 05 Oct 2021 12:49:20 GMT

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.

Re: Aggr with Above using if condition

agigliotti — Tue, 05 Oct 2021 13:10:04 GMT

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

Re: Aggr with Above using if condition

Digvijay_Singh — Wed, 06 Oct 2021 13:37:51 GMT

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

Re: Aggr with Above using if condition

tresB — Thu, 07 Oct 2021 09:14:12 GMT

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

Re: Aggr with Above using if condition

agigliotti — Thu, 07 Oct 2021 10:14:51 GMT

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

Re: Aggr with Above using if condition

Anil_Babu_Samineni — Mon, 01 Nov 2021 08:16:07 GMT

It's much clear now, Do you have any dashboard which demon the issue?