topic Re: sum over if statement with integraded set analysis function in QlikView

sum over if statement with integrated set analysis function

thanhphongle — Wed, 15 Nov 2017 12:47:18 GMT

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?

Re: sum over if statement with integraded set analysis function

Anil_Babu_Samineni — Wed, 15 Nov 2017 12:52:30 GMT

Create one inline table like

Load * Inline [

Dim

];

And use dimension like

Pick(Dim, contract_no, 'TOTAL')

Expression is

Pick(Dim,