Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Calculating Future Dates with value from another Dimension

Hello everyone,

I am trying to calculate future dates based on value inside the dimension and after creating the future dates it must contain a specific value comes from another dimension with the same table

here is a pic of what the data looks like and what is expected with explanation:

Untitled01.jpg

 

I tried ValueLoop but didn't work as expected. My main issue is how to copy the installment amount to all of those future dates.

EDIT: The required is to create a new field that has the installment amount for the next future dates. (future date is nothing but current month plus Remaining months) for every account id.

 

Thank you,

7 Replies
Highlighted
Master
Master

Hi Abdullah,

can you give more detail ?
for example Acount number 1, what do you expect?

Cheers,

Taoufiq

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Specialist II
Specialist II

maybe try IntervalMatch to generate a row for every month via a master calendar with the installment amount based on the opendate and loantenure, something like:

[loans]:

select accountId,

opendate,

addmonths(opendate,loantenure) as enddate

from loantable;

 

load month

Resident [Calendar];

intervalmatch(month)

load opendate, enddate

resident [loans]

 

https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

Highlighted
Partner
Partner

Hi Toufiq,

Account number is just a unique ID. I have no issues with it.

Each Account number has a fixed  installment amount, I'm trying to generate the same installment amounts for future dates for each account. Those future dates come from the RemainingMonths field added to today's MonthYear and each account number has one Value for this field as well. I hope I made things clearer

 

Best Regards,

 

Highlighted
Partner
Partner

I didn't try it yet, but I believe this will only generate the dates without having the installment amount for each date. 

 

Thanks,

Highlighted
Partner
Partner

in other words there must be some sort of loop maybe to copy the installment amount as long as the remaining months is greater than 0 AND loop(oct-2019, +1month) < oct-2019,+RemainingMonths

so for example an account, that has 3 remaining months and installment of 100$ then the new field will have data for the next 3 months,

Dec-2019, Jan-2019, Feb-2019 with 100$ each.

 

 

Highlighted
Master
Master

Sorry, abdulah.
I still can't understand it.
take an example of the file you sent.
what is the part you have in the data source? and what is the part you want to have after loading?

cheers,

Taoufiq

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Highlighted
Partner
Partner

Hi Toufiq,

Please check the attached excel sheet where I explained what is the data source and what is expected.

Thanks for the help,