sum over if statement with integrated set analysis function

[thanhphongle]

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?

[Anil_Babu_Samineni]

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

))))

[thanhphongle]

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?

[Anil_Babu_Samineni]

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

[sunny_talwar · Accepted solution]

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

[thanhphongle]

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.

[Anil_Babu_Samineni]

Sunny answer should be right this case