topic Re: Interest rates in QlikView

Interest rates

arsenal1983 — Sat, 16 Nov 2024 18:09:44 GMT

Hello Experts!
I have interesting task to solve. I’m trying to calculate the interest value and I have a problem to set the correct value of interest rate.

I have two tables
1. Deposits
a. ID
b. VALUE
c. START_DATE
d. END_DATE

2. Interest rate
a. DATE_FROM
b. INTREST_RATE

I’ve transformed those tables, add calendar, add reference table and finally I have got data model as attached.

The interest value are calculated monthly – so I add the list box with period (always one selected value).
The problem I have Is that the interest rate that should be used depends on deposit_start_date and deposit_end_date. Only one interest rate should be used for one deposit in specific month. The rule of determining the interest rate (model) is this:

MODEL	INTREST RATE
1	LAST DAY OF MONTH
2	START DAY
3	END DAY
4	END DAY

Example:
Interest value for 202003

ID	START_DATE	END_DATE	END_DATE_MOD	DEP_AMOUNT	INTEREST START DATE	INTEREST END DATE	INTEREST MODEL	INTEREST RATE DATE	INTEREST RATE VALUE
25	03.01.2020	-	01.09.2020	17 090,92	01.03.2020	31.03.2020	1	31.03.2020	1,17%
21	21.03.2020	-	01.09.2020	11 222,52	21.03.2020	31.03.2020	2	21.03.2020	1,18%
2	14.02.2020	03.03.2020	03.03.2020	12 729,68	01.03.2020	02.03.2020	3	02.03.2020	1,71%
42	03.03.2020	12.03.2020	12.03.2020	12 477,58	03.03.2020	11.03.2020	4	11.03.2020	1,69%

Anny suggestions how should I arrange that?

Regards,

Re: Interest rates

arsenal1983 — Tue, 08 Sep 2020 08:02:26 GMT

@sunny_talwar probably You are my last resort. Could You take a look at this?

Re: Interest rates

sunny_talwar — Tue, 08 Sep 2020 12:39:50 GMT

I was able to get the interest rates to show up, but I am confused as to why we are only looking to get 4 rows? why ID = 5 not show in your output? I am not sure why is that excluded?

Re: Interest rates

arsenal1983 — Tue, 08 Sep 2020 13:09:38 GMT

Hello @sunny_talwar those 4 records were only the examples 🙂

I feel we are close but for example for ID 21 the formula shows wrong value, in this case interest rate should be taken from start date or min(date_ref)

Re: Interest rates

sunny_talwar — Tue, 08 Sep 2020 13:21:07 GMT

May be use a different expression for each model....

Pick([INTEREST MODEL], Avg(Aggr(If(DATE_REF = Max(TOTAL <ID> DATE_REF), IR.IR), ID, DATE_REF)), Avg(Aggr(If(DATE_REF = Min(TOTAL <ID> DATE_REF), IR.IR), ID, DATE_REF)), Avg(Aggr(If(DATE_REF = Max(TOTAL <ID> DATE_REF), IR.IR), ID, DATE_REF)), Avg(Aggr(If(DATE_REF = Max(TOTAL <ID> DATE_REF), IR.IR), ID, DATE_REF)))

If only Model 2 has a different expression, you can use an if statement like this

If([INTEREST MODEL] = 2, Avg(Aggr(If(DATE_REF = Min(TOTAL <ID> DATE_REF), IR.IR), ID, DATE_REF)), Avg(Aggr(If(DATE_REF = Max(TOTAL <ID> DATE_REF), IR.IR), ID, DATE_REF)))

Re: Interest rates

arsenal1983 — Tue, 08 Sep 2020 13:43:04 GMT

@sunny_talwar You are my Hero! I owe you a good beer - let me know if you'll ever be in Poland. Big Thanks!