Formule Somme de multiplication avec conditions

[#1]

Bonjour à tous,

Je suis actuellement sur un sujet RH et je bloque sur ma formule de calcul des ETP...

Je souhaite donc calculer des ETP à partir des lignes de paie (table PAIE).

Cette table est composée des champs suivant:

- Etablissement

- Matricule Salarié

- ID Contrat

- Année_Paie

- Mois_Paie

- Code Rubrique

- Valeur

Mon numérateur est composée de plusieurs parties:

• Numérateur : (Somme de la rubrique présence) + (somme rubrique « heures supp » et « heures complémentaires ») + (somme des rubriques « absences » *  LA rubrique « Nombre d’heures par jour » si l’unité de la rubrique absence est en jour, sinon somme des rubriques «absences»)

Je souhaite créer une formule "classique" et également la même formule en set analysis (en figeant dans le mêmes tableaux les années N et N-1).

La partie qui me pose problème est la suivant: (somme des rubriques « absences » * rubrique « Nombre d’heures par jour » si l’unité de la rubrique absence est en jour, sinon somme des rubriques «absences»)

Actuellement je fais (uniquement pour les rubriques absences en jour):

=sum(if(TYPO_RUBRIQUE='Absences',Valeur,) * If(TYPO_RUBRIQUE='HEURES_PAR_JOUR',Valeur,))

--> la somme affiche 0

Si je fais:

=sum(if(TYPO_RUBRIQUE='Absences',Valeur,)) * sum( If(TYPO_RUBRIQUE='HEURES_PAR_JOUR',Valeur,))

--> ça marche quand je filtre sur un salarié (car une seule rubriques absences et une seule rubriques "heures par jour"), mais au niveau établissement forcément, ça somme d'un côté mon nombre de jours, que je multiplie par la somme de mon nombre d'heures par jour...

J'ai essayé différentes variantes avec la fonction "aggr" mais sans succès.

Je vous remercie d'avance.

[brunobertels]

Bonjour Vincent

Est ce que tu pourrais uploader ton application qlik sense , ou encore uploader un fichier avec un échantillon de tes données, se sera plus facile pour la communauté de t'aider pour ta question. ( Pour uploader un fichier : cliquer sur éditeur avancé en haut à droite puis sur joindre en bas à droite )

Pour ta formule , dans le champs TYPO_RUBRIQUE tu as la valeur 'Absences' et tu as aussi la valeur possible 'HEURES_PAR_JOUR'

Est bien exact ou 'HEURES_PAR_JOUR' est en fait une dimension comme TYPO_RUBRIQUE ?

De ce que je comprend :

Si 'HEURES_PAR_JOUR' est une valeur de la dimension TYPO_RUBRIQUE dans ce cas ta formule cherche à multiplier des lignes de ta base entre elle

Si 'HEURES_PAR_JOUR' est une dimension alors ta formule If(TYPO_RUBRIQUE='HEURES_PAR_JOUR' compare les valeurs entre ces deux champs. Or a ce que je comprend dans l'une il y a du texte ( Absence par exemple) dans l'autre un nombre d'heure .

Bruno

[#3]

Bonjour Bruno,

Petite précision tout d'abord: je suis sous QlikView

Pour répondre à ta question, effectivement je cherche à multiplier des lignes entre elles. Les valeurs "ABSENCES" et "HEURES_PAR_JOUR" sont des modalités de mon champs "TYPO_RUBRIQUE" (au même titre que toutes les lignes de paie comme le salaire brut, heures supplémentaires, etc...).

Du coup je cherche à faire la somme de la multiplication de lignes entre elles.

Est-ce possible?

Merci (PS: difficile de donner un jeu test rapidement sans le retravailler)

Vincent

[sfatoux72]

Ce serait plus propre d'avoir un champ par type de valeur.

Vous avez essayé comme ceci?

=Sum(Aggr(sum({$<TYPO_RUBRIQUE={'Absences'}>} Valeur) * Only({$<TYPO_RUBRIQUE={'HEURES_PAR_JOUR'}>} Valeur), Etablissement, [Matricule Salarié], [ID Contrat], Année_Paie, Mois_Paie))

[#5]

Bonjour Sébastien,

Merci pour votre réponse.

Les données sont extraites d'une table de paie avec des centaines de lignes de paie rattachées à un établissement/salarié/contrat/année/mois. Donc potentiellement des centaines de champs.

J'ai essayé la formule mais sans succès.

Ci-joint un extrait de cette table pour un salarié d'un établissement sur un mois/année.

    

Le but étant de faire la somme du champs "base" pour les  rubrique:  ("HEURES_CONV_NORM" - ("MATERNITE" * "HEURES_JOUR") ) /151,67

  ETP =  (140,83 - (30*4.69433333333)) / 151.67

[brunobertels · Accepted solution]

Bonjour

Pas sur que ce soit la bonne approche mais voila comment je procéderai :

charger la table complète :

[Feuil1]:

LOAD

  [FK_ETABLISSEMENT],

  [matricule],

  [idcontrat],

  [année_paie],

  [mois_paie],

  [TYPO_RUB],

  [base],

  [UNITE]

FROM [lib://Downloads/Test_ETP_RH.xlsx]

(ooxml, embedded labels, table is Feuil1);

Puis sortir une table avec

le nombre d'HEURES_JOUR par employé et par période , en utilisant 3 champs pour créer un ID

Heures:

load

  [idcontrat]&'|'& [année_paie]&'|'&[mois_paie] as ID ,

  [base] as NBRHeure

resident [Feuil1]

where [TYPO_RUB]='HEURES_JOUR'; // je ne prends que les lignes HEURES_JOUR

joindre les deux tables :

base:

left Join

load *,

[idcontrat]&'|'& [année_paie]&'|'&[mois_paie]as ID

resident [Feuil1]

where [TYPO_RUB]<>'HEURES_JOUR';

drop table [Feuil1];

puis je recharge l'ensemble en ajoutant un champs calculé :

Final:

load*,

if(UNITE ='J', base * NBRHeure , base) as INDICE

resident Heures;

drop table Heures;

drop field NBRHeure;

Du coup la formule ETP devient :

(Sum({$<TYPO_RUB={'HEURES_CONV_NORM'}>}INDICE)

-

Sum({$<TYPO_RUB-={'HEURES_CONV_NORM'}>}INDICE)

)

/

151.67

ou pour l'avoir sur une base mensuelle :

avg(aggr(

(Sum({$<TYPO_RUB={'HEURES_CONV_NORM'}>}INDICE)

-

Sum({$<TYPO_RUB-={'HEURES_CONV_NORM'}>}INDICE)

)

/

151.67,mois_paie))

[sfatoux72]

Bonjour Vincent,

tu trouveras en pièce jointe une application qvw dans laquelle j'ai chargé ton fichier Excel.

Vu que je suis en Suisse, j'ai modifié quelques valeur des paramètres environnemental afin que QlikView puisse lire correctement les nombres du champs base (séparateur de décimal ',') de ton fichier Excel.

Tu trouveras dans l'application un tableau avec l'expression qui réalise le calcul désiré. Qui donne 0 dans ce cas.

[sfatoux72]

Sinon, en créant dynamiquement des champs distincts pour chaque mesure.

On diminue le nombre de ligne de données et on simplifie les expressions:

• Avant :
  • =Sum(Aggr(Round((sum({$<TYPO_RUB={'HEURES_CONV_NORM'}>} base) - (sum({$<TYPO_RUB={'MATERNITE'}>} base) * Sum({$<TYPO_RUB={'HEURES_JOUR'}>} base))) /151.67, 0.01), FK_ETABLISSEMENT, matricule, idcontrat, année_paie, mois_paie))
    
• Après :
  • =Sum(Aggr(Round((sum(HEURES_CONV_NORM) - (sum(MATERNITE) * Sum(HEURES_JOUR))) /151.67, 0.01), FK_ETABLISSEMENT, matricule, idcontrat, année_paie, mois_paie))

Dans l'application jointe, tu trouveras le script de chargement avec l'utilisation de l'instruction Generic qui fait tout le travail.

J'ai commenté la partie du code effectuant la reprise des unité, ne sachant pas si elles sont nécessaires ou pas.

[#9]

Bonjour,

Avec beaucoup de retard...Désolé...

Merci beaucoup, c'est une très bonne méthode !

J'ai une question concernant les macros: j'avais déjà créé des macros pour exporter automatiquement des tableaux/reporting dans des fichiers Excel, à actionner avec un bouton. Est-il possible que cette macro s'exécute dans un script, lors d'un rechargement avec un Reload programmé par QMC ?

Merci d'avance.

Vincent

[brunobertels]

Bonjour

Alors là, j'en sais rien du tout... J'utilise qliksense et pas de macro

J'espère que les experts qlikview sauront te répondre car pas bien compris ce que tu veux faire exactement.

Tu veux une macro qui t'exporte des tableaux sous Excel au moment / en même temps que le reload?

Bon courage en tous cas

Bruno