Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
this is my database
contract_number | contract_start_date | contract_end_date | contract_term | contract_term_type | contract_payment_perc |
---|---|---|---|---|---|
100001 | 01.01.2012 | 31.12.2014 | 36 | first | 3,11% |
100001 | 01.01.2015 | 30.06.2015 | 6 | second | 2% |
100001 | 01.07.2016 | 31.12.2016 | 6 | third | 1,5% |
100002 | 01.01.2013 | 31.12.2016 | 48 | first | 2,66% |
100002 | 01.01.2017 | 31.12.2017 | 12 | second | 1,5% |
100002 | 01.01.2018 | 30.06.2018 | 6 | third | 0,5% |
100003 | 01.01.2017 | 31.12.2019 | 36 | first | 3,05% |
100003 | 01.01.2020 | 30.06.2020 | 6 | second | 1,9% |
100003 | 01.07.2020 | 31.12.2020 | 6 | third | 1,45% |
I created a variable v_target_date to calculate my present value depended on the variable v_target_date
I created in the script a new field:
lpm_history:
LOAD *,
if(contract_term_type='first', Monthstart(AddMonths(contract_start_date, contract_term)+1)) as contract_begin_ext_simulation;
My expression :
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=0,
(PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1),
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1), sum({<contract_term_type={'second'}>} contract_payment_perc),0,1)*-1,
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1) > sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'second'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)-sum({<contract_term_type={'third'}>} contract_term), sum({<contract_term_type={'third'}>} contract_payment_perc),0,1)*-1
)))
i marked the set analysis in red
This is what QV returns
Everything is right so far
What I'd like to do in the next step is to take the sum of all
-> 210,14%+164,65%+119,59% = 494,38% and return the result in a textbox
unfortunetely I can't just add a sum() in front of my expression.
Is there any possibility to do that?
Can you try this....
Keep the dimension same as Anil mentioned, but change your expression to this
Sum(Aggr(if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=0,
(PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1),
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1), sum({<contract_term_type={'second'}>} contract_payment_perc),0,1)*-1,
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1) > sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'second'}>}contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)-sum({<contract_term_type={'third'}>} contract_term),sum({<contract_term_type={'third'}>} contract_payment_perc),0,1)*-1
))), Contract_Number))
Create one inline table like
Load * Inline [
Dim
1
2
];
And use dimension like
Pick(Dim, contract_no, 'TOTAL')
Expression is
Pick(Dim,
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=0,
(PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1),
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1), sum({<contract_term_type={'second'}>} contract_payment_perc),0,1)*-1,
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1) > sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'second'}>}contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)-sum({<contract_term_type={'third'}>} contract_term),sum({<contract_term_type={'third'}>} contract_payment_perc),0,1)*-1
))),
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=0,
(PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} TOTAL contract_payment_perc),0,1)*-1),
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=sum({<contract_term_type={'second'}>} TOTAL contract_term),
PV(0.01/12, sum({<contract_term_type={'first'}>} TOTAL contract_term), sum({<contract_term_type={'first'}>} TOTAL contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1), sum({<contract_term_type={'second'}>} TOTAL contract_payment_perc),0,1)*-1,
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1) > sum({<contract_term_type={'second'}>} TOTAL contract_term),
PV(0.01/12, sum({<contract_term_type={'second'}>} TOTAL contract_term), sum({<contract_term_type={'first'}>} TOTAL contract_payment_perc),0,1)*-1+
PV(0.01/12, sum({<contract_term_type={'second'}>} TOTAL contract_term), sum({<contract_term_type={'second'}>} TOTAL contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)-sum({<contract_term_type={'third'}>} TOTAL contract_term),sum({<contract_term_type={'third'}>} TOTAL contract_payment_perc),0,1)*-1
))))
Hello Anil , thanks for your help
I just follow ur instructions. But QV returns me this
the result should be:
210,14%+164,65%+119,59% = 494,38% and return the result in a textbox
and could u maybe explain me what the function dim() is exactly doing?
When you say, You expression works. That means The above may work as expected. And, I just throw a stone but no luck for you. Will you able to provide sample?
Try with your expression like
Pick(Dim, [Your Expression], 1000)
And show the image / it would be great if you share sample file
Can you try this....
Keep the dimension same as Anil mentioned, but change your expression to this
Sum(Aggr(if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=0,
(PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1),
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)<=sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'first'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1), sum({<contract_term_type={'second'}>} contract_payment_perc),0,1)*-1,
if(((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)>0 AND
((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1) > sum({<contract_term_type={'second'}>} contract_term),
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'first'}>} contract_payment_perc),0,1)*-1+
PV(0.01/12, sum({<contract_term_type={'second'}>} contract_term), sum({<contract_term_type={'second'}>}contract_payment_perc),0,1)*-1+
PV(0.01/12, ((year(v_target_date)-year(contract_begin_ext_simulation))*12+(month(v_target_date)-month(contract_begin_ext_simulation))+1)-sum({<contract_term_type={'third'}>} contract_term),sum({<contract_term_type={'third'}>} contract_payment_perc),0,1)*-1
))), Contract_Number))
Thank you for your reply.
It works with the sum(aggr(x,contract_number) function. Now I dont know who I should give the right answer measured by the engagement.
Sunny answer should be right this case