10 Replies Latest reply: Jun 27, 2017 4:18 AM by Vincent MAGNAUDEIX RSS

    Formule Somme de multiplication avec conditions

    Vincent MAGNAUDEIX

      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.

       

        • Re: Formule Somme de multiplication avec conditions
          bruno bertels

          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

            • Re: Formule Somme de multiplication avec conditions
              Vincent MAGNAUDEIX

              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

            • Re: Formule Somme de multiplication avec conditions
              Sébastien Fatoux

              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))

               

               

               

                • Re: Formule Somme de multiplication avec conditions
                  Vincent MAGNAUDEIX

                  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

                    • Re: Formule Somme de multiplication avec conditions
                      bruno bertels

                      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))

                        • Re: Formule Somme de multiplication avec conditions
                          Vincent MAGNAUDEIX

                          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

                            • Re: Formule Somme de multiplication avec conditions
                              bruno bertels

                              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

                                • Re: Formule Somme de multiplication avec conditions
                                  Vincent MAGNAUDEIX

                                  Bonjour,

                                   

                                  Oui, j'ai une macro qui exporte des tableaux sous Excel. J'actionne cette macro avec un bouton actuellement.

                                   

                                  Mais je pensais pouvoir actionner cette macro dans mon script (par exemple un "call MaMacro"). De cette manière je LOAD tous mes QVD, je les transforme, je fais mon modèle dans le script et ma dernière commande de mon script serait d'appeler ma macro pour qu'elle exporte automatiquement mon tableau. Car celui ci alimente une autre application...

                                   

                                  Je ne sais pas si c'est faisable...Pour le moment je n'ai trouvé que des appels de fonctions dans un script et pas de macro...

                                   

                                  Vincent

                            • Re: Formule Somme de multiplication avec conditions
                              Sébastien Fatoux

                              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.

                                • Re: Formule Somme de multiplication avec conditions
                                  Sébastien Fatoux

                                  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.