Discussion :

[Davidb_]

Bonjour,

Je souhaite réaliser un tableau présentant l’historique du solde des clients débiteurs par Années/Mois. Pour cela je dois calculer pour chaque clients/Mois/Années le solde du compte et afficher la valeur de ce solde lorsque celui-ci est négatif. Chaque mois il faut recalculer le solde du client et vérifier si celui-ci est débiteur ou non.


J’ai un graphique histogramme avec en dimension un groupe hiérarchique Année/Mois et en expression :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
sum({<CodeNatureCompteComptable={C},Libelle_client={"=
sum(
aggr(
		sum({<CodeNatureCompteComptable={C}>}MontantEcriture)
		,AnnéeJourneeComptable,MoisJourneeComptable,Libelle_client)
)<0"}
 
>}MontantEcriture)*-1

Je ne suis pas très loin du résultat avec cette expression je pense, mais pour certaines années j’obtiens quand même des valeurs négatives (donc positives en réalité à cause du *-1)

j’ai coché l’option « Cumul Total »


Merci d’avance

[PhunkyBob]

Bonjour,

Si votre client "Toto" est débiteur en janvier et en avril, que doit afficher votre graphique ?
Uniquement les valeurs pour les mois où il est débiteur, ou les valeurs pour tous les mois (parce que vous voulez afficher les comptes de tous les clients qui ont au moins une fois un compte débiteur) ?

[Davidb_]

Dans ce cas la si je suis en selection sur Toto mon graphique devra afficher uniquement le mois de janvier et d'avril avec le montant du solde du compte de toto pour à ces deux periodes.

Uniquement les valeurs pour les mois où il est débiteur, ou les valeurs pour tous les mois (parce que vous voulez afficher les comptes de tous les clients qui ont au moins une fois un compte débiteur) ?

Je ne veux que les valeurs pour lesquels les clients ont un solde débiteur.

[PhunkyBob]

Et donc, en "Cumul Total", vous n'attendez que la somme des débits de janvier et avril ? Ou la somme de tous les mois ?


Si je prends les données suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MontantEcriture:
LOAD * INLINE [
CodeNatureCompteComptable, Libelle_client, AnnéeJourneeComptable, MoisJourneeComptable, MontantEcriture
C, Toto, 2014, 1, -100
C, Toto, 2014, 2, 10
C, Toto, 2014, 3, 0
C, Toto, 2014, 4, -50
C, Toto, 2014, 5, 50
C, Plop, 2014, 1, 200
C, Plop, 2014, 2, 100
C, Plop, 2014, 3, 0
C, Plop, 2014, 4, -100
C, Plop, 2014, 5, -200
C, Bob, 2014, 1, 100
C, Bob, 2014, 2, 100
C, Bob, 2014, 3, 100
C, Bob, 2014, 4, 100
C, Bob, 2014, 5, 100
];

et que je fais un graphique "tableau croisé" avec comme dimension "AnnéeJourneeComptable", "MoisJourneeComptable" et "Libellé_client", et comme expression

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
if (
	sum({$ <CodeNatureCompteComptable={'C'}>} MontantEcriture) < 0,
	sum({$ <CodeNatureCompteComptable={'C'}>} MontantEcriture)
)

J'obtiens bien les chiffres attendus :

[Davidb_]

Et non ce serait trop simple

Avec ton expression on n'obtient pas le solde du compte. En fait il faut recalculer tous les mois le solde du client c'est à dire que l'on reprend toutes ses ecritures (l'Historique complet) et la on peut regarder si le client est débiteur ou non.
Le "Cumul total" coché est la pour ça mais je me demande si il ne faudrait pas le forcer avec une expression dans le set analysis...

[quote]Et donc, en "Cumul Total", vous n'attendez que la somme des débits de janvier et avril ? Ou la somme de tous les mois ?[quote] C'est donc la somme de tous les mois lorsque cette somme est négative.

[PhunkyBob]

OK, je croyais que les données étaient déjà pré-agrégées dans le modèle.

Je change donc mon exemple avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
MontantEcriture:
LOAD * INLINE [
CodeNatureCompteComptable, Libelle_client, AnnéeJourneeComptable, MoisJourneeComptable, MontantEcriture
C, Toto, 2014, 1, -100
C, Toto, 2014, 2, 100
C, Toto, 2014, 2, 10
C, Toto, 2014, 3, -10
C, Toto, 2014, 3, 0
C, Toto, 2014, 4, -50
C, Toto, 2014, 5, 50
C, Toto, 2014, 5, 50
C, Plop, 2014, 1, 200
C, Plop, 2014, 2, -100
C, Plop, 2014, 3, -100
C, Plop, 2014, 4, -100
C, Plop, 2014, 5, -100
C, Bob, 2014, 1, 100
C, Bob, 2014, 2, 0
C, Bob, 2014, 3, 0
C, Bob, 2014, 4, 0
C, Bob, 2014, 5, 0
];
 
DimAnnee:
LOAD DISTINCT AnnéeJourneeComptable AS DimAnnee
RESIDENT MontantEcriture;
 
 
DimMois:
LOAD DISTINCT MoisJourneeComptable AS DimMois
RESIDENT MontantEcriture;

Je fais un graphique avec comme dimensions : DimAnnée, DimMois, Client et comme expression

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
if (
	sum({$ <CodeNatureCompteComptable={'C'}>} if(makedate(AnnéeJourneeComptable, MoisJourneeComptable) <= makedate(DimAnnee, DimMois), MontantEcriture)) < 0,
	sum({$ <CodeNatureCompteComptable={'C'}>} if(makedate(AnnéeJourneeComptable, MoisJourneeComptable) <= makedate(DimAnnee, DimMois), MontantEcriture))
)

c'est à dire que pour chaque mois, je recalcule le montant total de toutes les périodes précédentes.

Et j'obtiens le résultat attendu :

[Davidb_]

La on ne fait le cumul que du mois en cours, or il faut recalculer en prenant tout l'historique des ecritures du client depuis sa création dans la base.

En prenant ton dernier échantillon de données voici le résultat attendu:




Je pense que l'expression doit etre dans cet esprit la (mais la syntaxe ne passe pas...)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
sum({<CodeNatureCompteComptable={C},Libelle_client=
{"=
sum(
	aggr(
		sum({<CodeNatureCompteComptable={C}, MoisAnnéeJourneeComptable={"<=MoisAnnéeJourneeComptable"} >}MontantEcriture)
		,AnnéeJourneeComptable,MoisJourneeComptable,Libelle_client)
)<0"}
 
>}MontantEcriture)*-1

[PhunkyBob]

La on ne fait le cumul que du mois en cours, or il faut recalculer en prenant tout l'historique des ecritures du client depuis sa création dans la base.

En prenant ton dernier échantillon de données voici le résultat attendu:

Je ne comprends comment vous faites pour obtenir ce résultat.

Si Toto a :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
C, Toto, 2014, 1, -100
C, Toto, 2014, 2, 100
C, Toto, 2014, 2, 10
C, Toto, 2014, 3, -10
C, Toto, 2014, 3, 0
C, Toto, 2014, 4, -50
C, Toto, 2014, 5, 50
C, Toto, 2014, 5, 50

En janvier, j'ai "-100".
En février, j'ai les "-100" de janvier + "100" de février + "10" de février, donc (pour moi) un total de 10 (donc un montant positif que je n'affiche pas).
En mars, j'ai les "10" d'avant, et "-10" de mars et "0" de mars --> 0 --> pas affiché.
En avril : 0 - 50 = -50 --> négatif donc affiché
En mai : -50 + 50 + 50 = 50 --> positif donc pas affiché



Je ne comprends pas comment vous arrivez à "-90".

[Davidb_]

A pardon je n'ai pas fait attention aux deux lignes du mois de février le -90 est en trop effectivement !

[Davidb_]

Voici les résultats que j'obtiens avec tes données et ton expression

[PhunkyBob]

Un Set Analysis fait un filtre avant le calcul "ligne par ligne".
Il faut voir ça comme un remplacement des sélections de l'utilisateur, pas comme une condition de calcul.


Qu'attendez-vous de la partie SA qui change le filtre client ?
Ce SA ne pourra pas donner "une liste de clients pour le mois 1, une autre liste de clients pour le mois 2, etc...".
Un SA de ce genre aurait un sens si votre règle d'affichage était quelque chose du style "à partir du moment où un client a été au moins une fois dans le rouge, alors je veux qu'on affiche sa courbe".

[PhunkyBob]

Voici les résultats que j'obtiens avec tes données et ton expression

Il faut aussi changer les dimensions !

Je fais un graphique avec comme dimensions : DimAnnée, DimMois, Client et comme expression

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
if (
	sum({$ <CodeNatureCompteComptable={'C'}>} if(makedate(AnnéeJourneeComptable, MoisJourneeComptable) <= makedate(DimAnnee, DimMois), MontantEcriture)) < 0,
	sum({$ <CodeNatureCompteComptable={'C'}>} if(makedate(AnnéeJourneeComptable, MoisJourneeComptable) <= makedate(DimAnnee, DimMois), MontantEcriture))
)

c'est à dire que pour chaque mois, je recalcule le montant total de toutes les périodes précédentes.

[Davidb_]

Qu'attendez-vous de la partie SA qui change le filtre client ?

Cette partie la doit faire:

Pour chaque Mois/Années je cumul tout l'historique (donc tout ce qui inferieur ou égal au MoisAnnée), je regarde si ce cumul est négatif, si celui-ci est négatif je l'affiche.

Ce SA ne pourra pas donner "une liste de clients pour le mois 1, une autre liste de clients pour le mois 2, etc...".

c'est ça qu'il me faut

EDIT :

Il faut aussi changer les dimensions !

A ça marche !
Je vais regarder ce que ça donne avec mes données !

[Davidb_]

Alors cela fonctionne si j'utilise un TCD avec les bonnes dimensions mais pour mon histogramme ça risque d'etre un peu compliqué.

Je regarderai ça certainement demain.

Merci !

[Davidb_]

La méthode des dimensions dates libres avec la fonction makedate fonctionne bien mais cela demande énormément de ressource à chaque actualisation du graphique. Ce n’était pas possible à gérer donc je suis passé par une autre méthode :
J’ai créé un champ historique solde client dans mon script, je cumul toutes les écritures du compte client à chaque nouveau mouvement.
Ensuite avec la fonction IntervalMatch j’ai pu faire en sorte de remonter le solde client à une date donnée.

[PhunkyBob]

C'est sur que s'il y a beaucoup de lignes, un pré-calcul au chargement est toujours plus efficace qu'un gros calcul à chaque sélection de l'utilisateur