Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

Optimization of expression for KPI usage

Hello, Qlikers

 

I have been wondering the past few hours how to best optimize an expression, which has to be put in a KPI, and depending on user selection on 2 fields( StartDate and EndDate), it calculates the amount, which has been repaid, out of the expected values. 

My datamodel is 1 fact table with all expected values and 1 table with repayments inside it. 

the expression is the following : 

sum(total aggr(if((sum(aggr(round(Sum({<duedate={"<=$(=Only(EndDate))"},type_res={'delay_interest'}>}amount), 0.01)
-sum({<income_date_res={"<=$(=Only(ReportDate))"},type_res={'delay_interest'}>}paid_sum_res),installment_table_id_res)))>=
Sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'delay_interest'}>}amount),0,
if(Round(Sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'delay_interest'}>}amount), 0.01)>0.01,sum(aggr(if(
sum({<income_date_res={"<=$(=Only(ReportDate))"},type_res={'delay_interest'}>}paid_sum_res)>=
Round(Sum({<duedate={"<=$(=Only(EndDate))"},type_res={'delay_interest'}>}amount), 0.01),
Sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'delay_interest'}>}amount),round((
sum(aggr(sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'delay_interest'}>}amount)
-(
Sum({<duedate={"<=$(=Only(EndDate))"},type_res={'delay_interest'}>}amount)
-sum({<income_date_res={"<=$(=Only(ReportDate))"},type_res={'delay_interest'}>}paid_sum_res)),installment_table_id_res))
),0.01)),installment_table_id_res)))),installment_table_id_res))

 

It gives me the right result, each sum of amount, has to be aggregated by installment_table_id_res, and check in repayment if this specific installment is paid, if it's paid it has to get the value from expected table, because sometimes repayments are more than expected values. 

So in general, i am at a crossroads. Any input guys? 

I have tried to do this calculation in the load script, but i couldn't manage it. 


Thanks in advance for your time 😉 

PS: Because of client policies and nda's i can't publish any example data..

 

1 Reply
Partner
Partner

Re: Optimization of expression for KPI usage

basically the expression is following:
TE- Total expected values in the period chosen

TP- Total paid values in the period chosen

 

if(TE-TP>=TE,0,

if(TE>0,01,

sum(if(TP>=TE,TE,

TE-(TP-TE)))))