Lignes cumulées (12 mois) par ligne dans un tableau

[florence2000]

Bonjour à tous,

Je viens vers vous car je cherche depuis plusieurs jours une formule qui me permettrait d'avoir pas ligne, le cumul sur 12 mois.

Je m'explique :

J'ai un tableau dynamique sur 12 mois où j'ai une colonne CA, Encours et DSO (calcul en fonction de l'épuisement de l'encours).

Je voudrais avoir un 4eme colonne qui me donnerait la moyenne de mon DSO mais sur 12 mois glissant.

càd si je sélectionne

Année = 2017

Mois = 07

J'obtiens un tableau avec comme dimension la concatenation de Mois +  Année de août 2016 à Juillet 2017 avec CA , Encours et DSO des 12 derniers mois pour chaque ligne . Jusque là pas de problème ...

Je joins en exemple mon tableau

Je voudrais avoir par exemple pour Juil 2017 , la moyenne glissante des 12 mois (Août 2016 à Juil 2017) soit 60,22 (comme dans mon tableau)

Pour Juin 2017, je voudrais la moyenne glissante du DSO (Juil 2016 à Juin 2017),....

Quelqu'un serait-il m'aider ?

Merci bcp

[kevinchevrier]

RangeAvg (Above(MyField),0,12))

Exemple à la fin de cette page :https://help.qlik.com/fr-FR/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/RangeFunctions/ra...

[sfatoux72]

Bonjour Florence,

La réponse de Kevin va te donner la réponse correcte que pour la dernière colonne de ton tableau. En effet, Above est une fonction Inter-enregistrement (https://help.qlik.com/fr-FR/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/InterRecordFuncti...) d'un tableau, donc même si l'expression spécifie bien de faire la moyenne sur les 12 cellules précédentes à partir de la cellule actuelle, seul la dernière colonne a ces 12 cellules à disposition.

Il faudrait appliquer cette expression dans un tableau contenant toutes les année-mois et n'en rendre visible que les 12 mois désirés.

On peut faire ceci en utilisant la fonction AGGR pour créer un tableau virtuel et des set analysis pour que ce tableau ne soit pas impacté par des sélection temporelle.

Tu n'as pas donné l'expression pour le calcul de DSO, je vais donc utiliser dans mon exemple :  Sum(val_DSO)

L'expression pour le calcul de la moyenne glissante du DSO sur 12 mois serai quelque chose comme :

Only( Aggr({$<Année=, Mois=>} RangeAvg(Above(Sum({$<Année=, Mois=>} val_DSO ),0,2)), Année,  Mois))

Note :

• L'Aggr doit avoir la même granularité que le tableau dans lequel il est utilisé, afin d'avoir une correspondance direct entre le tableau virtuel (Aggr) et le tableau. Il peut avoir une granularité plus fine si l'expression interne à l'Aggr le demande, dans ce cas il faudra remplacer le Only par une fonction d'agrégation comme Sum, Avg,  
• Si tu as d'autres dimensions temporelle que l'utilisateur peut utiliser comme sélection (comme Date), il faudra l'ajouter au set analyasis :  {$<Année=, Mois=>}   ==> {$<Année=, Mois=, Date=>}

[florence2000]

Bonjour Kevin,

Merci pour ton aide mais effectivement, cela ne fonctionne pas .

Cette formule ne peut donner que des résultat qui se trouve sur le tableau et mon calcul doit prendre en compte 12 mois glissant  même si cela n'apparaît pas dans le tableau.

[florence2000]

Bonjour Sébastien,

Ta formule me semble assez près de ce que je voudrais mais le souci c'est que je n'arrive pas à faire un set analysis sur ma formule du DSO car elle est assez longue et complexe.

Mon tableau est dynamique en fonction du Mois et de l’année sélectionnée

Exple , je choisis juillet, j’ai un tableau reprenant mes valeurs de août 2016 => Juil 2017

Si je sélectionne, le mois de juin, j’ai Juil 2016 => Juin 2017

Le problème de ma colonne DSO est que le calcul est assez long et « complexe »

************************************************************************************************************************

if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))<0,
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))/
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))*30,

if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)<0,
((Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))/
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1))*30+30,


if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)<0,
(((sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1))/
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2))*30+60,


if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)<0,
(((Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2))/
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3))*30+90,

if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4)<0,
(((Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3))/
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4))*30+120,

if(Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2= >}$(Var_Encours))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC5)<0,
(((Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_Encours))-
Sum({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}$(Var_CA_TVAC)))-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC1)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC2)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC3)-
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC4))/
Sum ({$<MoisSequentiel = {">$(#=(Max(MoisSequentiel)-12))<=$(#=Max(MoisSequentiel))"}, Monthnum=, Year2=>}NetSales_TVAC5))*30+150,'>180 days'))))))

**********************************************************************

J’ai essayé de le mettre en variable mais du coup, il ne tient plus compte du nombre de Mois séquentiel ( = nombre qui correspond aux dates)

De plus si je veux l’utiliser dans un Set Analysis , ce n’est pas possible puisque ma variable fait déjà une somme (je suis obligée pour que le résultat soit correct)

Merci bcp pour ton aide

Florence

[sfatoux72]

Florence,

Que veux-tu dire par "...ma variable fait déjà une somme ..." ?

Je pense avoir une solution pour toi, mais peux-tu me transmettre :

• L'expression utilisée dans la dimension calculée "Year_Month"
• Le contenu de la variable Var_Encours
• Le contenu de la variable Var_CA_TVAC

Est-ce que tous les champs utilisés dans ton expression (dans les sum) se trouvent dans la même table dans ton modèle de données? Eventuellement transmettre une copie d'écran du diagramme des tables 

[sfatoux72]

Autre chose,

pour faire la moyenne de DSO mensuel sur 12 mois, il faudrait que l'expression DSO retourne toujours une valeur numérique.

On ne peut pas laisser faire une moyenne avec  '> 180 days'

[florence2000]

Bonjour Sébastien,

Tout d'abord, je voulais m'excuser de mon silence, j'au dû "m'absenter" quelques mois.

Es-tu toujours d'accord de m'aider sur ce problème car malheureusement il ne s'est pas réglé tout seul lol

Florence

[sfatoux72]

‌si tu me fournit les informations que je t’avais demandé , j’essayerai de t’aider

[florence2000]

Bonjour Sébastien,

Merci beaucoup d'avoir répondu.

Entre-temps, j'ai fait un truc à la Mc Gyver qui a l'air de fonctionner.

J'ai réussi à créer une formule directement dans le script.

Merci beaucoup en tout cas !

Florence