Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
arsenal1983
Creator
Creator

Interest rates

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:

modele.PNG

MODELINTREST RATE
1LAST DAY OF MONTH
2START DAY
3END DAY
4END DAY

 

Example:
Interest value for 202003

IDSTART_DATEEND_DATEEND_DATE_MODDEP_AMOUNTINTEREST START DATEINTEREST END DATEINTEREST MODELINTEREST RATE DATEINTEREST RATE VALUE
2503.01.2020-01.09.202017 090,9201.03.202031.03.2020131.03.20201,17%
2121.03.2020-01.09.202011 222,5221.03.202031.03.2020221.03.20201,18%
214.02.202003.03.202003.03.202012 729,6801.03.202002.03.2020302.03.20201,71%
4203.03.202012.03.202012.03.202012 477,5803.03.202011.03.2020411.03.20201,69%

 

Anny suggestions how should I arrange that?

Regards,

 

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

5 Replies
arsenal1983
Creator
Creator
Author

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

sunny_talwar

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?

image.png

arsenal1983
Creator
Creator
Author

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)

sunny_talwar

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

View solution in original post

arsenal1983
Creator
Creator
Author

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