Optimization of expression( aggr or no aggr)

[tomovangel]

Hello, guys 

I haven't posted in a while, but I am at a crossroad, and I need your help. 

My data model is a fact table with connection to Repayments and Expected values. 

my in app data is around 100 mil rows. 

I have a table with 3 dimensions( Loan Number, Loan Period, Loan Status) and 20 expressions, which are calculating each different type of Repayments. 

The expressions I am using differ from one another only in type_res field. 

I am wondering which of the 2 expression's formulas will optimize my load, because when I try to generate for bigger period it goes to " Error out of calculation memory" .

StartDate and EndDate, are fields which the user choses and bases the report on, the first sorted value if is to sort loans which are still active at the time of report generation. 

My first set of expression is written like this :

if(FirstSortedValue(distinct {<generated_r1={"<=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'written_off'
and FirstSortedValue(distinct {<generated_r1={"<=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'closed_written_off',
Sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'commission_fee'}>}amount))...

The second set of expression which gives me right data is : 
sum(aggr(if(FirstSortedValue(distinct {<generated_r1={"<=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'written_off'
and FirstSortedValue(distinct {<generated_r1={"<=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'closed_written_off',
Sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'commission_fee'}>}amount))
,[Loan Number])
)

So, my question is which of the 2 expression is going to be calculated faster. ( I have 19 more expression just like those, but with a different type_res field.

[JordyWegman]

Hi,

What you can try is getting this in your script and instead of using AGGR, use group by. Prepare this information and concatenate this to your fact table.

Jordy

Climber

[Ivan_Bozov]

Hello,

AGGR could slow down your app quite a lot. As suggested, you could try to move the calculation (or some part of it) to the script. Also this here is a good read: https://community.qlik.com/t5/Qlik-Design-Blog/When-should-the-Aggr-function-NOT-be-used/ba-p/146785...

[tomovangel]

Thanks, but I can not do this calculation in the script, since it requires selection of dates. Any other input? 

[sunny_talwar · Accepted solution]

Can you check if the below expression gives the same answer as your second expression. Because if it does, you can use this as it will work better than both the two expressions

Sum({<
  duedate = {">=$(=Only(StartDate))<=$(=Only(EndDate))"},
  type_res ={'commission_fee'},
  [Loan Number] = {"=not Match(FirstSortedValue(DISTINCT {<generated_r1 = 
  {[<=$(=Only(EndDate))]}>} status_r1, -generated_r1time), 'written_off', 
  'closed_written_off')"}
>} amount)

[tomovangel]

Hi Sunny, Once again your expressions are amazing, it works great 😉  let me update all 20 expression with your logic and i will check the response time, but I am optimistic for this. it looks really clean and good. 


Thanks !!!!!

[tomovangel]

Hi, Sunny 
I have another question

I have another expression like this : 

sum(aggr(if(FirstSortedValue(distinct {<generated_r1={">=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'written_off'
and FirstSortedValue(distinct {<generated_r1={"<=$(=Only(EndDate))"}>} status_r1,-generated_r1time)<>'closed_written_off',
Sum({<duedate={">=$(=Only(StartDate)) <=$(=Only(EndDate))"},type_res={'restructured'}>}amount))
,[Loan Number])
)

The difference from the first is the >= sign in the first ( Firstsortedvalue)

Sum({<
          duedate = {">=$(=Only(StartDate))<=$(=Only(EndDate))"},
          type_res ={'restructured'},
          [Loan Number]= {"=not Match(FirstSortedValue(DISTINCT {<generated_r1 =
          {[>=$(=Only(EndDate))]}>} status_r1, -generated_r1time),'written_off')"},
          [Loan Number]= {"=not Match(FirstSortedValue(DISTINCT {<generated_r1 =
          {[<=$(=Only(EndDate))]}>} status_r1, -generated_r1time),'closed_written_off')"}
 >} amount)

But It does not give me right result, where is the mistake? 

I have tried to write it like this : 

[sunny_talwar]

May be this

Sum({<
          duedate = {">=$(=Only(StartDate))<=$(=Only(EndDate))"},
          type_res ={'restructured'},
          [Loan Number]= {"=not Match(FirstSortedValue(DISTINCT {<generated_r1 =
          {[>=$(=Only(EndDate))]}>} status_r1, -generated_r1time),'written_off', 'closed_written_off')"}
 >} amount)

[tomovangel]

no, it has to be for written off >=  

and for closed_written_off <= . 

But Let me try something and if I can't i will write to you for help. 

I will try to include something from the backend here. 

thanks again 

[sunny_talwar]

My bad, did not see that difference... try this

Sum({<
      duedate = {">=$(=Only(StartDate))<=$(=Only(EndDate))"},
      type_res ={'restructured'},
      [Loan Number]= {"=not (Match(FirstSortedValue(DISTINCT {<generated_r1 = {[>=$(=Only(EndDate))]}>} status_r1, -generated_r1time),'written_off') and Match(FirstSortedValue(DISTINCT {<generated_r1 = {[<=$(=Only(EndDate))]}>} status_r1, -generated_r1time),'closed_written_off'))"}
 >} amount)