set filter

Tanya_In — Wed, 02 Mar 2022 10:01:27 GMT

Hello everyone! Sorry for my English, I'm writing with a translator)

Please tell me in the next question:

It is necessary to calculate the amount of balances by beneficiary_bank_name in the context of "year to previous year".

This is the formula for the selected year:

sum(Aggr (Sum(Aggr( If(escrow_balance_date = Max(TOTAL <escrow_agreement_id,escrow_balance_date.autoCalendar.Year> {$<[escrow_balance_date.autoCalendar.Year]={"$(=date(max(escrow_balance_date.autoCalendar.Year),'YYYY'))"}>}escrow_balance_date), escrow_balance_sum), escrow_agreement_id, escrow_balance_date)), beneficiary_bank_name))

//we set the maximum date under the contract (because one contract may have several opening dates)

//if the date of the contract in the selected year coincides with the maximum date of the contract of this year, then we take the amount of this contract

//grouping data by agreement id, amount and date of agreement and beneficiary_bank_name

And there is a formula for the previous year:

=sum(Aggr(Sum(Aggr(

If(escrow_balance_date =

Max(

TOTAL <escrow_agreement_id,escrow_balance_date.autoCalendar.Year>

{<[escrow_balance_date.autoCalendar.Year]={'$(#vLastYear)'}>}escrow_balance_date),

escrow_balance_sum),

escrow_agreement_id,

escrow_balance_date)),

beneficiary_bank_name))

// where vLastYear= date(AddYears(max(escrow_balance_date.autoCalendar.Year),-1),'YYYY')

When we do not select anything in the filter, the data is output correctly:

But, when we select 2021 in the filter, for example, to compare with 20, the previous year will be empty, due to the fact that filtering by escrow_balance_date and 20 year does not fall.

how can this filtering be avoided so that all the dates for comparison are included. We tried to do it through Resident Load to add a table with dates, and through variables in the table, but this also does not work correctly.

I attach a file with the report. Thank you all in advance

topic set filter in App Development

set filter