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