Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Tanya_In
Contributor
Contributor

set filter

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:

1.png

 

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.

2.png

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

Labels (3)
0 Replies