calculate a weighted average

[anissabnk]

Hello, I would like to calculate a weighted average on an average call time.

The logs I have available are of this type:

I want to be able to obtain the calculation of the average time this way

The formula applied is as follows:

Here is what I have done so far:

index=rcd statut=OK partenaire=000000000P 
| eval date_appel=strftime(_time,"%b %y") 
| dedup nom_ws date_appel partenaire temps_rep_max temps_rep_min temps_rep_moyen nb_appel statut tranche_heure heure_appel_max 
| eval nb_appel_OK=if(isnotnull(nb_appel) AND statut="OK", nb_appel, null()) 
| eval nb_appel_KO=if(isnotnull(nb_appel) AND statut="KO",nb_appel, null()) 
| eval temps_rep_min_OK=if(isnotnull(temps_rep_min) AND statut="OK", temps_rep_min, null()) 
| eval temps_rep_min_KO=if(isnotnull(temps_rep_min) AND statut="KO",temps_rep_min, null()) 
| eval temps_rep_max_OK=if(isnotnull(temps_rep_max) AND statut="OK", temps_rep_max, null()) 
| eval temps_rep_max_KO=if(isnotnull(temps_rep_max) AND statut="KO",temps_rep_max, null()) 
| eval temps_rep_moyen_OK=if(isnotnull(temps_rep_moyen) AND statut="OK", temps_rep_moyen, null()) 
| eval temps_rep_moyen_KO=if(isnotnull(temps_rep_moyen) AND statut="KO",temps_rep_moyen, null()) 
| stats sum(nb_appel_OK) as nb_appel_OK, sum(nb_appel_KO) as nb_appel_KO min(temps_rep_min_OK) as temps_rep_min_OK, min(temps_rep_min_KO) as temps_rep_min_KO max(temps_rep_max_OK) as temps_rep_max_OK, max(temps_rep_max_KO) as temps_rep_max_KO, values(temps_rep_moyen_OK) AS temps_rep_moyen_OK, values(temps_rep_moyen_KO) as temps_rep_moyen_KO values(nom_ws) as nom_ws, values(date_appel) as date_appel 
| eval temps_rep_moyen_KO_calcul=sum(temps_rep_moyen_KO*nb_appel_KO)/(nb_appel_KO) 
| eval temps_rep_moyen_OK_calcul=sum(temps_rep_moyen_OK*nb_appel_OK)/(nb_appel_OK) 
| fields - tranche_heure_bis , tranche_heure_partenaire 
| sort 0 tranche_heure 
|table nom_ws partenaire date_appel nb_appel_OK nb_appel_KO temps_rep_min_OK temps_rep_min_KO temps_rep_max_OK temps_rep_max_KO temps_rep_moyen_OK temps_rep_moyen_KO

I cannot get the final average_ok time displayed

temps_moyen= [(nb_appel_1 * temps_moyen 1)+(nb_appel_2 * temps_moyen 2)+...)/sum of nb_appel .

I really need help please.

Thank you so much

[yuanliu]

The illustrated search is rather confusing. (Also, data illustration is better done with text, not screenshots.) First, the original data contains fields tranche_heure and partenaire, but not tranche_heure_partenaire or tranche_heure_bis.  So, I will ignore related manipulations.  Second, I am not sure if there is a point of preserving all raw values of temp_rep_moyen.  But because your search does so, I will preserve them as temp_rep_moyen_orig.

Most importantly, handling the difference between statut values "OK" and "KO" before performing stats functions just make the job so much harder.  You should use groupby instead.

index=rcd statut=OK partenaire=000000000P 
| eval date_appel=strftime(_time,"%b %y") 
| dedup nom_ws date_appel partenaire temps_rep_max temps_rep_min temps_rep_moyen nb_appel statut tranche_heure heure_appel_max
| stats avg(eval(temps_rep_moyen * nb_appel)) as temps_rep_moyen sum(nb_appel) as som_nb_appel max(temps_rep_max) as temps_rep_max min(temp_rep_min) as temps_rep_min
  values(temps_rep_moyen) as temps_rep_moyen_orig values(nom_ws) as nom_ws, values(date_appel) as date_appel by statut
| eval temps_rep_moyen = temps_rep_moyen / som_nb_appel
| fields - som_nb_appel

The above search will give you two rows of weighed temps_rep_moyen as you defined, temps_rep_max, temps_rep_min, and aggregated values of other fields including original values of temp_rep_moyen.  The first row corresponding to statut "KO", the second row, statut "OK".  Hope this helps.

If you really need the fields to be named after statut, do so after this stats. (I'll leave that as your exercise.)