Why a calculation in the report dont give the same result in the load script

[bentley32]

Hello all 🙂

I am trying to do a simple calculation 

=sum(x)/sum(y)

when i do it in the report i got a good results but when i do it in the load script like with aggregations, it doesnt work at all, iv got wrong numbers, i do aggregation on the load script on the lowest level

Thanks

[Or]

It's not clear what exactly you're doing...

Sum(x)/Sum(y) at the script level will give you the result aggregated by whatever is in the GROUP BY clause of the load statement.

Sum(x)/Sum(y) at the front-end level will give you the result aggregated by the dimensions used in the object and limited by any selections applied. If no dimensions are used (typically only in KPI objects) it'll be the total for all selections.

[bentley32]

actually i made a group by in the load script by all of the dimensions, because they are needed for filtering

[bentley32]

If i dot aggregate just by the dimensions that i have in my pivot table of the report i will need to do it just with the dates that i have in my pivot and i am loosing all of the potential filtering 

[Or]

Sorry, you'll have to provide more specific examples of what exactly you're doing (script and object level) for me to have an idea of what you're doing and what the problem is. What's the data? What's the load statement? What's the object structure of your pivot table? If your expression is Sum(x)/Sum(y), you aren't ignoring selections, so is your expression actually something else?

[bentley32]

Load departement,
departement2,
tranche_ca,
naf_client_niveau1,
naf_client_niveau2,
naf_client_niveau3,
naf_client_niveau4,
naf_client,
direction,
service,
nom_regroupement_client,
nom_client,
num_centrale,
num_client,
spid,
vendeur,
motif_cloture,
naf_debiteur_niveau1,
naf_debiteur_niveau2,
naf_debiteur_niveau3,
naf_debiteur_niveau4,
naf_debiteur,
score_et_decision,
score_amiable,
known_unknown,
clients_avec_jud,
type_debiteur,
pays_de_residence,
departement_deb,
psa_m1,
pays_de_naissance,
age_debiteur,
age_dossier,
annee_pec,
mois_pec,
inexploitable,
original_capital,
principal,
m_since_reg,
duree_de_gestion_mois,
registration_date,
case_id,
sum(enc_c0)/sum(principal) --> my calculation

from [lib://France Local Data/loienc.qvd] (qvd)

group by
departement,
departement2,
tranche_ca,
naf_client_niveau1,
naf_client_niveau2,
naf_client_niveau3,
naf_client_niveau4,
naf_client,
direction,
service,
nom_regroupement_client,
nom_client,
num_centrale,
num_client,
spid,
vendeur,
motif_cloture,
naf_debiteur_niveau1,
naf_debiteur_niveau2,
naf_debiteur_niveau3,
naf_debiteur_niveau4,
naf_debiteur,
score_et_decision,
score_amiable,
known_unknown,
clients_avec_jud,
type_debiteur,
pays_de_residence,
departement_deb,
psa_m1,
pays_de_naissance,
age_debiteur,
age_dossier,
annee_pec,
mois_pec,
inexploitable,
original_capital,
principal,
m_since_reg,
duree_de_gestion_mois,
registration_date,
enc_c1,
enc_c0,
case_id

And same calculation in the pivot table sum(enc_c0)/sum(principal) , and i do have as a dimension annee_pec,
mois_pec

[Or]

If your pivot dimensions are only annee_pec and mois_pec, you are going to get the sum for those two dimensions, not for the entire long list of dimensions that you have in the group by clause.

[bentley32]

it means that i will lose the functionalities of filtering with my long list of fields ?

[Or]

Your sum is going to respect whatever filter you make, but ultimately, the result is going to be the sum for the specific value of annee_pec and mois_pec in that pivot row, whereas your Load statement will get the calculation for each unique combination of values across all of the group by fields (meaning it's a lot more detailed, presumably). If you tried to do the same thing in your pivot, you would get an array of numbers instead of a single result (if desired this can be achieved using the aggr() function, but I'm not sure why you'd want to do that).

[bentley32]

i want it in the load script for performance ... how can i get it then with the aggr ? i couldnt find out