Perform aggregation at backend qliksense

[Aspiring_Developer]

Hello All,

Request you to please help mw with below scenario :-

I have below set of expression in qliksense chart and it is taking too long to load and at the end getting 'calculation time out error" :-

1)if(GetSelectedCount(Periode)=0, Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_TOTAL),
avg(aggr(Sum({<KPI_Code={'KPI101'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'}>} Nbr_TOTAL),Periode,Entity)))

After breaking the expression , i got to know that the second expression of else part is the root cause :-

avg(aggr(Sum({<KPI_Code={'KPI101'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'}>} Nbr_TOTAL),Periode,Entity)))

This formula is taking too long the load the data inside chart.

Below is the data model and the fields coming from each table :-

I want to do this calculation in backend , but not sure how to do it.

I tired to below :-

and then did this in front end expression :-

avg(Aggr({<KPI_Code={'KPI101'}>}Test, Entity))

But it didn't worked.

Am i not following the correct approach? Please help .

Thanks in advance.

[stevejoyce · Accepted solution]

Your fields you are aggr'ing on, should be you joining key (composite key) in your data model and what you are grouping by.  I also am assuming there are many _KEY_DIM to 1 Entity, so you should map KEY_DIM to Entity first.  I added another step in the middle that converts the composite of Periode+Entity to a numeric value.

So you should have a new table joined to Fait on Key_Entity_Periode

map_entity_key_dim:
mapping load distinct
_KEY_DIM
,Entity
resident ORGA
;

temp1_PossibleCombinations:
Load distinct
_KEY_DIM
,Periode
,Periode & '|' & applymap('map_entity_key_dim', _KEY_DIM) as text_Key_Entity_Periode
resident Fait;

temp2_DistinctValues:
mapping load distinct
text_Key_Entity_Periode
,RowNo() as Key_Entity_Periode
resident temp1_PossibleCombinations;

drop table temp1_PossibleCombinations;

//join numeric key to Fait table
left join (Fait)
load distinct
_KEY_DIM
,Periode
,applymap('temp2_DistinctValues', Periode & '|' & applymap('map_entity_key_dim', _KEY_DIM)) as Key_Entity_Periode
resident Fait;

aggr_entity_periode:
Load distinct
Key_Entity_Periode
,Sum(Nbr_KO) / Sum(Nbr_TOTAL) as aggr_entity_periode_Nbr_percent_of_total
Resident Fait
group by Key_Entity_Periode
;

exit script;

[Aspiring_Developer]

Hi @stevejoyce 

Thank you for sharing the solution . I tried your approach , however i am not able to get the correct values. 

For instance:-

Original Formulae -

if(GetSelectedCount(Periode)=0, Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_TOTAL),
avg(aggr(Sum({<KPI_Code={'KPI101'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'}>} Nbr_TOTAL),Periode,Entity)))

After doing the else part aggregation at backend , and then applying the new field in front end , i should get the same result as i was getting using old formula , however my number is significantly decreased like below :-

Using this expression -

if(GetSelectedCount(Periode)=0, Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_TOTAL),
avg(aggr_entity_periode_Nbr_percent_of_total))

Can you please help ?

[Aspiring_Developer]

@stevejoyce 

Just to add , according to the old expression :-

if(GetSelectedCount(Periode)=0, Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'},Periode={'$(=$(Maxscorecard) )'}>} Nbr_TOTAL),
avg(aggr(Sum({<KPI_Code={'KPI101'}>} Nbr_KO)/Sum({<KPI_Code={'KPI101'}>} Nbr_TOTAL),Periode,Entity)))

If i am not doing any selection on filter 'Periode', then my value should be :-

And when we do any selection 'Periode' = Aug 2021, then it should give below :-

Also , right now my entire value is decreased .

[stevejoyce]

I did not filter to KPI_Code={'KPI101'}, did you add this to the load script I sent?

aggr_entity_periode:
Load distinct
Key_Entity_Periode
,Sum(Nbr_KO) / Sum(Nbr_TOTAL) as aggr_entity_periode_Nbr_percent_of_total
Resident Fait

where KPI_Code='KPI101'
group by Key_Entity_Periode
;

[Aspiring_Developer]

Hi @stevejoyce 

KPI_Code is coming from Fait Table and it has below values :-

I passed the value in set analysis like below :-

Avg({<KPI_Code={'KPI101'}>} aggr_entity_periode_Nbr_percent_of_total)

I did this because i need to calculate this expression for all KPI's . Still my value have reduced .

[stevejoyce]

Those are logically different though.  In your load script we are calculating the sum for each period/entities, and then you are only averaging where there are period/entities that have a KPI101.

Your front-end calculation you are only summing only for KPI101.

If you want to distinguish for each KPI, then you should add KPI_Code to your composite key (in both your groupby & join field).