12 Replies Latest reply: Oct 6, 2016 8:13 AM by Mikael Voisine RSS

    Calcul effet prix dans le script

    Mikael Voisine

      Bonjour

       

      J'ai créé une appli commerciale et donc il faut que je fasse des calculs d'effet prix (volume et mix également)

      Dans un set analysis, ma formule d'effet prix fonctionne correctement

       

      sum(Aggr(

      (sum({$<Année={$(=Max(Année))},[Gratuit LFACT]={N},[Code Contrat Codif]={1,5},N3_hors_filiales={1},Flag_Creneau={0},Poids_positif={0}>}[CA NFranco])

      /sum({$<Année={$(=Max(Année))},[Gratuit LFACT]={N},[Code Contrat Codif]={1,5},N3_hors_filiales={1},Flag_Creneau={0},Poids_positif={0}>}[Poids Facturé])

      -sum({$<Année={$(=Max(Année)-1)},[Gratuit LFACT]={N},[Code Contrat Codif]={1,5},N3_hors_filiales={1},Flag_Creneau={0},Poids_positif={0}>}[CA NFranco])

      /sum({$<Année={$(=Max(Année)-1)},[Gratuit LFACT]={N},[Code Contrat Codif]={1,5},N3_hors_filiales={1},Flag_Creneau={0},Poids_positif={0}>}[Poids Facturé]))

      *sum({$<Année={$(=Max(Année))},[Gratuit LFACT]={N},[Code Contrat Codif]={1,5},N3_hors_filiales={1},Flag_Creneau={0},Poids_positif={0}>}[Poids Facturé])

      ,[SF Planif],[SFamille Marque],[Nom Groupe Prévision N3]

      ,Promo,[Etat du Produit],[RC Code Produit],Creneau))

       

      J'aimerai passer directement par le script pour me calculer cette formule pour ne pas refaire cette formule pour chaque graphique mais je coince

      Je sais qu'il faut passer par un group by mais je ne comprend pas comment faire un année N - année N-1 par exemple

        • Re: Calcul effet prix dans le script
          bruno bertels

          Bonjour

           

          Tu peux peut être contourner le problème en mettant ta formule dans une variable

          Et du coup appeler ta variable dans tes différents tableaux ou graphique.

           

          Ou plus simplement créer ta mesure en master item ?

           

          Bruno

            • Re: Calcul effet prix dans le script
              Mikael Voisine

              Bonjour et merci de ta réponse

              J'avais bien pensé faire une variable des mes effets mais ça ne fonctionnait pas (sur l'effet volume il me semble)

              Je vais réessayer

              Par contre dis m'en plus sur les master item car je n'en n'ai jamais entendu parler (je m'autoforme car pas grand monde dans ma société pour répondre à mes interrogations et c'est pourquoi j'ai besoin de la communauté)

               

              De plus je pensais passer directement dans le script car mon appli est en test et il faut régulièrement rajouter une nouvelle donnée et donc modifier à la main mes formules et donc une multitude de graph à chaque fois

                • Re: Calcul effet prix dans le script
                  bruno bertels

                  Bonjour :

                   

                  ÉLÉMENTS PRINCIPAUX ou MASTER ITEM

                   

                  Tu peux utiliser les éléments principaux ( MASTER ITEM) comme ceci :

                   

                  tu peux créer un élément principal soit de dimension , soit de mesure.

                   

                   

                  Tu ajoutes ton expression

                  Tu la nommes et clique sur créer

                   

                  Ensuite tu la retrouves dans les éléments principaux tu peux la glisser déposer ou la rechercher par son nom pour l'ajouter dans un tableau ou graphique.

                  Si tu la modifie dans les éléments principaux , elle sera automatiquement modifiée partout ou la mesure est intégrée.

                   

                  VARIABLES :

                  tu accèdes à l'éditeur de variable en bas a gauche de ton écran qliksense :

                   

                  Tu as cette fenêtre :

                   

                   

                  Tu donnes un nom à ta variable et ajoute ta formule ou ton expression dans le champ Définition

                   

                  par exemple vEffetPrix en nom et ta formule

                   

                  puis dnas les tableaux ou graphiques tu appelles ta variable comme ceci :

                  $(vEffetPrix)

                   

                  Idem si tu as besoin de modifier ta formule il te suffit de la modifier dans l'éditeur de variable et elle se mettra à jour partout ou elle est utilisée.

                   

                  Selon moi c'est plus facile de passer par les éléments principaux ou l'éditeur de variable que par le script.

                  Enfin pour t'aider avec le group by , çà m'est difficile car comme toi suis assez nouveau sur qlik sense et surtout pas développeur , j'ai donc galéré au début mais le forum m'a beaucoup aidé.

                   

                  Si tu es à l'aide en anglais tu peux reposer ta question en anglais dans la section new to qliksense , pour augmenter tes chances.

                   

                  Bon courage

                   

                  Bruno

              • Re: Calcul effet prix dans le script
                Christophe Brault

                Salut Mickael,

                 

                Je vois que tu continue à bien t'amuser !

                 

                Je pense que tu devrais faire ces calculs dans le script, comme pour le tableau de bord hebdo.

                 

                Ton aggr() fais les calculs au plus fin, et si tu l'utilise à plusieurs endroits, va vite plomber les performances de l'appli.

                 

                Tu as combien de tables dans ton modèle ? Partage le si tu peux.

                 

                A bientôt

                  • Re: Calcul effet prix dans le script
                    Mikael Voisine

                    Salut

                    ça fait un bail

                    Oui je continu à m'éclater

                    Comme m'a suggéré Bruno, j'ai mis mes formules d'effets dans des variables que j'appelle ensuite dans les différents graph

                    Je pense que c'est mieux au niveau perf

                    Je pense comme toi que c'est au niveau du script qu'il faudrait que je calcule ces effets mais je bloque

                    je n'arrive pas à faire année N - année N-1 par ex

                    Je charge 2 fichiers à plats (un par année) et je n'ai donc qu'une seule table (au grand dam du service info qui souhaite que je fasse un modèle en étoile )

                     

                    Je ne sais pas comment poster un fichier sur ce forum donc je te colle mon script ci dessous

                     

                    ///$tab Main

                    SET ThousandSep=' ';

                    SET DecimalSep='.';

                    SET MoneyThousandSep=' ';

                    SET MoneyDecimalSep='.';

                    SET MoneyFormat='# ##0.00 €;-# ##0.00 €';

                    SET TimeFormat='hh:mm:ss';

                    SET DateFormat='DD/MM/YYYY';

                    SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                    SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

                    SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

                     

                     

                    SET ThousandSep=' ';

                    SET DecimalSep=',';

                    SET MoneyThousandSep=' ';

                    SET MoneyDecimalSep=',';

                    SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

                    SET TimeFormat='hh:mm:ss';

                    SET DateFormat='DD/MM/YYYY';

                    SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                    SET MonthNames='jan.;fév.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

                    SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

                    SET HidePrefix = '_';

                     

                     

                    //COULEURS

                    SET vRouge = '=rgb(218,18,21)';

                    SET vBleu = '=rgb(36,166,208)';

                    SET vBleufoncé = '=rgb(0,88,162)';

                    SET vBleuAutre = '=rgb(83,128,176)';

                    SET vMauve = '=rgb(174,84,184)';

                    SET vOrange = '=rgb(244,73,17)';

                    SET vVert = '=rgb(152,175,69)';

                    SET vjaune = '=rgb(237,193,18)';

                    SET vMarron = '=rgb(80,40,45)';

                    SET vGrisnoir = '=rgb(57,57,57)';

                    SET vGrismoyen = '=rgb(188,188,188)';

                    SET vGrisclair = '=rgb(232,232,232)';

                     

                     

                     

                     

                    //FONCTIONNEMENT GENERAL APPLICATION // Variables d'actions

                    SET HidePrefix=ID;

                    LET vShowFilter ='0';

                    LET vShowHelp ='0';

                    LET vShowSelectionActive='0';

                    LET vPath = '..\..\..\qlikview-data\GastronomeTdBHebdo\';

                     

                     

                     

                     

                     

                     

                    //======================================================================================================

                    //*** INFORMATION ***

                    //PROPRIETAIRE : Mikaël VOISINE

                    //DESCRIPTION : Cette application permet d'analyser les ventes

                    //SOURCES : Fichiers QVD

                    //

                    //======================================================================================================

                    //*** INTERVENTIONS ***

                    //28 04 2016 - VOISINE Mikaël : Création des scripts de chargement

                    //

                    //======================================================================================================

                    //*** REGLES DE DEVELLOPEMENT ***

                    //Champs : La première lettre en majuscules (Ex : Année, Montant)

                    //Variables : Commence par un v devant les variables (Ex : vAnnéeCourante)

                    //Nom des clés : En majuscule, Commence par ID_ et masqué par HidePreFix (Ex : ID_CLIENT)

                    //

                    //======================================================================================================

                    //

                    //

                    //===================================================================================================================================================

                    //Définition variables pour la synthèse

                    // vDimension => permet de choisir les différentes dimension de comparaison (graph camembert & histogramme)

                    // vDimension=0 => Choix Produit

                    // vDimension=1 => Choix Client

                    // vDimension=2 => Choix Marque

                    //

                    // vIndicateur => permet de choisir son indicateur de comparaison (graph camembert & histogramme)

                    // vIndicateur=0 => Choix Volume

                    // vIndicateur=1 => Choix Chiffre d'affaire

                    // vIndicateur=2 => Choix marge brute

                    // vIndicateur=3 => Choix EBIT

                    //

                    // vMenu => permet de d'afficher le menu de navigation

                    //

                    //

                    //Définition variables pour les onglets détail & commerciaux

                    // vChoix... => permet de faire apparaitre/disparaitre les différents critère de séléction

                    // vChoixProduit => Sélection/déselection des Produits

                    // vChoixClient => Sélection/déselection des Clients

                    // vChoixmarque => Sélection/déselection des Marques

                    // vChoixPromo => Sélection/déselection Promo(O)/Hors Promo(N)

                    // vChoixEtatProduis => Sélection/déselection Frais/Gel

                    //

                    //

                    //Définition variables pour l'onglet Analyse des écarts

                    // vEffet => permet de choisir l'effet pour analyse

                    // vEffet=1 => Choix de l'effet prix

                    // vEffet=2 => Choix de l'effet volume

                    // vEffet=3 => Choix de l'effet mix

                    //

                    // vPériodeCompare => permet de comparé par rapport au budget ou au réel N-1

                    // vPériodeCompare=1 => Comparaison vs N-1

                    // vPériodeCompare=2 => Comparaison vs budget

                    //

                    //

                    //Définition variables pour l'onglet Benchmark

                    // vInd_Benchmark => permet de choisir l'indicateur de comparaison

                    // vInd_Benchmark=0 => Choix du taux de marge brute

                    // vInd_Benchmark=1 => Choix du PU NF

                    // vInd_Benchmark=2 => €/Kg de MSCV

                    //

                    //

                    //===================================================================================================================================================

                     

                     

                     

                     

                     

                     

                     

                     

                    ///$tab Outils

                    //PERMET DE CHOISIR SON UNTIE DE MESURE

                    Mode_Unité:

                    LOAD * INLINE

                      [Unité

                        M€

                        K€

                        €

                    ];

                     

                     

                    REGROUPEMENT_CRENEAU:

                    LOAD * INLINE [

                        Creneau, Regroupement_Creneau

                        GMS, GMS

                        CHD, BtoB

                        DIRECTION CIALE EQUILIBRE, Direction Ciale Equilibre

                        DISTRIBUTION SPECIALISEE, BtoB

                        EXPORT, Export

                        INDUSTRIE, Industrie

                        PAI, BtoB

                        INTRAGROUPE, Intragroupe

                    ];

                     

                     

                    MAP_client_N6:

                        mapping LOAD * INLINE [

                        old, new

                        ALDI, ALDI

                        ATAC, EUROCHAN

                        AUCHAN, EUROCHAN

                        CARREFOUR, CARREFOUR

                        CARREFOUR AFFILIES, CARREFOUR

                        CARREFOUR MARKET, CARREFOUR

                        CRF MARKET, CARREFOUR

                        CASINO GEANT, EMC

                        CASINO PETITES SURFACES, EMC

                        CASINO SUPER, EMC

                        CORA France, PROVERA

                        CRF MARKET AFFILIES, CARREFOUR

                        DEGAGEMENT, DIVERS GMS

                        DIVERS EMC, EMC

                        DIVERS EUROCHAN, EUROCHAN

                        DIVERS GMS, DIVERS GMS

                        DIVERS INAUDI, DIVERS GMS

                        DIVERS PROVERA, PROVERA

                        ED, CARREFOUR

                        FRANCAP, PROVERA

                        FRANPRIX, EMC

                        HOURA, PROVERA

                        INAUDI, DIVERS GMS

                        INAUDI HORS GMS, DIVERS GMS

                        INTERMARCHE, INTERMARCHE

                        LEADER PRICE, EMC

                        LECLERC, LECLERC

                        LIDL, LIDL

                        LUCHE TRADITION VOLAILLE, EMC

                        MATCH France, PROVERA

                        MONOPRIX, EMC

                        NETTO, INTERMARCHE

                        PERSONNEL/ELEVEUR, DIVERS GMS

                        PROXI, CARREFOUR

                        SCHIEVER, EUROCHAN

                        SIMPLY CHRONODRIVE, EUROCHAN

                        SYSTEME U, SYSTEME U

                        SYSTEME U COOP, SYSTEME U

                        TRAD ET COLLECTIVITES, DIVERS GMS

                        TRANSPORTEUR, DIVERS GMS

                    ];

                     

                     

                    REGROUPEMENT_FILIERE:

                    LOAD * INLINE [

                        Filière Produit, Regroupement_Filière

                        AUTRES VOLAILLES, Filière Autres volailles

                        BIOLOGIQUE, Filière Autres volailles

                        CANARD, Filière Canard

                        COPRODUITS CANARD, Filière Canard

                        COPRODUITS POULET, Filière Poulet Classique

                        COQ ET POULE, Filière Autres volailles

                        DINDE, Filière Dinde

                        PINTADE LABEL, Filière SSQ

                        POULET CERTIFIE, Filière SSQ

                        POULET FILIERE, Filière SSQ

                        POULET LABEL, Filière SSQ

                        POULET VEGETAL, Filière Poulet Classique

                        PRODUITS ELABORES CRUS, Filière Pe Crus

                        PRODUITS ELABORES CUITS, Filière Pe Cuits

                        VOLAILLES FESTIVES, Filière SSQ

                    ];

                    ///$tab Fait

                    AGENDA:

                    LOAD MAJ,

                      num(Month(Date(LISTE_MOIS))) as MOIS_AGENDA,

                      Year(date(LISTE_MOIS)) as ANNEE_AGENDA,

                      Week(date(LISTE_MOIS)) as SEMAINE_AGENDA

                     

                    FROM

                    [M:\qlikview-data\GastronomeTdBHebdo\Commerce\Sécurité.xlsx]

                    (ooxml, embedded labels, table is Agenda_mois);

                     

                     

                     

                     

                     

                     

                    VENTES_TMP:

                    LOAD *

                    FROM $(vPath)Commerce\Ventes_Globales\*.qvd(qvd)

                    //Where [Code Société]='67'

                    ;

                     

                     

                     

                     

                     

                     

                    VENTES:

                    LOAD MakeDate([Année Facturation],Right([Mois Facturation],2),1) as ID_Date,

                      [Code Société],

                      [Fam Planification],

                      Creneau,

                         if(Creneau<>('INTRAGROUPE'),0,1) as Flag_Creneau,

                         if(Creneau='GMS',ApplyMap('MAP_client_N6',[Nom Groupe Prévision N5]),[Nom Groupe Prévision N5]) as [Client N6],

                         [Nom Groupe Prévision N5],

                         [Etat du Produit],

                         [SF Planif],

                         [Famille Marque],

                         [Filière Produit],

                         Promo,

                         [RC Code Produit],

                         [Nom Groupe Prévision N4],

                         [SFamille Marque],

                         [Nom Groupe Prévision N3],

                         if([Nom Groupe Prévision N3]=('ALLEMAGNE GMBH') or [Nom Groupe Prévision N3]=('ANGLETERRE GASTRONOME UK'),0,1) as N3_hors_filiales,

                         [Libellé Produit],

                         [Unité de Fabrication],

                         [Unité Préparation],

                         [Gratuit LFACT],

                         Dégagement,

                         [Type Marque],

                         Gamme,

                         if(IsNull([Code Contrat Codif]),'1',[Code Contrat Codif]) as [Code Contrat Codif],

                         [Poids Facturé],

                         if([Poids Facturé]>0,0,1) as Poids_positif,

                         [Pièce Fact],

                         [CA NFranco],

                         [Cout Transport],

                         [Marge Brute Projeté],

                         [MSCV projeté],

                         [MSCC Projeté],

                         [RC Code Produit]&' '&[Libellé Produit] as [Code Produit]

                     

                     

                    Resident VENTES_TMP;

                    DROP Table VENTES_TMP;

                     

                     

                    Join

                    HIERARCHIE_COMMERCIALE :

                    LOAD [Code Société],

                      Creneau,

                      [Type Marque],

                         [Etat du Produit],

                         [Nom Groupe Prévision N5],

                         Gamme,

                         [SFamille Marque],

                         [Filière Produit],

                         [Direction Commercial],

                         [Compte Clé],

                         AutoNumberHash128([Code Société],[Direction Commercial]) as ID_Sécurité

                    FROM

                    [M:\qlikview-data\GastronomeTdBHebdo\Commerce\Hierarchie_commerce.xlsx]

                    (ooxml, embedded labels, table is [Rapport 1]);

                     

                     

                    ///$tab Calendrier

                     

                    LET vDateMin = num(Makedate(2015,01,01)); 

                    LET vDateMax = num(Makedate(2016,12,31));

                     

                     

                    TempCalendar: 

                    LOAD

                    //Génération de toutes les dates possibles entre la date min et la date max

                    $(vDateMin) + RowNo() - 1 AS DateNumber, 

                    Date($(vDateMin) + RowNo() - 1) AS TempDate 

                    AUTOGENERATE 1 

                    WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

                     

                     

                    //Création d'une table qui va contenir les différents composants du calendrier

                    Calendar: 

                    LOAD

                    (Date(TempDate)) AS Date, 

                    num(Date(TempDate)) AS ID_Date,

                    // Standard Date Objects 

                    WeekDay(TempDate) AS Jour, 

                    Week(TempDate) AS Semaine, 

                    Month(TempDate) AS Mois,

                    num(Month(TempDate)) AS NoMois, 

                    'T' & Ceil(Month(TempDate)/3) AS Trimestre, 

                    Year(TempDate) AS Année,

                    MonthName(TempDate)as Lib_Mois,

                    num(month(TempDate),'00')&'/'&num(Year(TempDate),'00')as AnnéeMois,

                    date(TempDate,'YYYY-MM') as Lib_AnnéeMois

                     

                     

                    RESIDENT TempCalendar ORDER BY TempDate ASC; 

                     

                     

                     

                    DROP TABLE TempCalendar; 

                     

                    @+

                      • Re: Calcul effet prix dans le script
                        Christophe Brault

                        Le fait de mettre toàn expression en variable ne change rien niveau performance. Le calcul ne se fait pas une fois lorsque tu utilise le $(), mais dans chaque objet.

                         

                        Pour le modèle, tu peux dire à la DSI de ma part que non. Qlik fonctionne en vectoriel et non relationnel. Le meilleur modèle théoriquement est une seule grosse table en terme de performance.

                         

                        Pour faire du N vs N-1 dans le script, il faut que tu crée un champs par année en faisant un concatenate puis un group by.

                         

                        C'est ce qui est fait dans le tableau de bord hebdo pour les calculs de perf.

                         

                        Je n'ai pas le temps de voir ton script en détail dsl...

                    • Re: Calcul effet prix dans le script
                      Puttemans Johan

                      Bonjour Mikael,

                       

                      Il doit être possible de créer une table additionelle, qui reprends les totaux que vous utilisez dans le dashboard. Celle là sera toujours fixe, dans le sens ou un YTD différent que le dernier jour/mois (selon script) sera difficile.

                       

                      Vous savez toujours combiner un group by avec un where, et donc scripter

                       

                      LOAD

                           Y

                           sum(X)

                      FROM...

                      WHERE year =...

                      group by Y;

                      • Re: Calcul effet prix dans le script
                        Cyrielle Letteron

                        Bonjour,

                         

                        Pour pouvoir faire un calcul AnnéeN - AnnéeN-1 :

                        Il faut ajouter un colonne contenant les données de l'années N-1grace à un Left Joint (voir ci dessus) vous pourrez ensuite faire votre calcul puis supprimer la colonne.

                         

                        LEFT JOIN (TABLE)

                        LOAD

                             Année+1 as Année,

                             // Tous les champs rendant la ligne unique,

                             Valeur as ValeurN-1

                        RESIDENT TABLE;

                         

                        Attention :

                        Si vos lignes ne sont pas uniques (SOMME faite au premier chargement) cela ne fonctionnera pas.