TCD - compter champ 1 si champ 2 non vide - Qlik Community

delphinewalter · ‎2019-07-24

Bonjour,

j'ai un problème avec une fonction de count(distinct...).

j'ai un tableau qui me met pour chaque année le CA généré par les nouveaux clients exclusivement de l'année en question. Je voudrais pouvoir compter le nombre de nouveaux clients correspondants.

le tableau actuellement

Capture.png = le tableau actuellement.

voici ma formule de décompte des clients :

if(sum({$<TIERS_annee_creation={'$(=max(CalendarYear)-1)'},V_annee={'$(=max(CalendarYear)-1)'}>}V_CA_HT)>0,1,0)*count({$<TIERS_annee_creation={'$(=max(CalendarYear)-1)'},V_annee={'$(=max(CalendarYear)-1)'}>}distinct TIERS_code)

et voici le résultat que ça me donne (et mon problème) :

j'ai bien 1 seul nouveau client à qui j'ai réellement facturé quelque chose. Il y a un autre TIERS_code qui a reçu des choses mais non facturées (et qui d'ailleurs n'apparaît pas dans le tableau) : je considère donc que ce n'est pas un client à proprement parlé.

Comment faire pour que le nombre de la catégorie parente ("eshop" dans ma capture d'écran), soit égal au nombre de clients "réels" ? en logique, je ferais "pour chaque client, si la somme du CA > 0, compte 1 sinon compte 0" et ensuite la somme de tout ça, mais je n'arrive pas à le traduire en formule (peut-être que la logique n'est pas la bonne non plus ?).

Merci pour votre aide !

sylvain_piccarreta · ‎2019-07-24

Bonjour,

qu'apparait-il comme client dans tes selections si tu cliques sur le 2 ?

as-tu un exemple de ligne sources générant ton tableau ? car si sur chaque ligne, tu as l'année, tu pourrais alors ajouter un champ cpt_NVX_clients lors du chargement de ton script afin de mettre 1 si CA > 0, sinon 0, et tu comptes/filtres dessus.

delphinewalter · ‎2019-07-24

Bonjour Sylvain,

si je clique sur le 2, j'ai juste ma capture d'écran. si je regarde dans la table Tiers, j'ai bien les 2 clients qui restent, celui à qui on a réellement facturé quelque chose, et celui à qui on a envoyé gratuitement des choses.

c'est peut-être bête, mais je ne vois pas comment mettre dans mon script un champ nouveau client 🤔 parce que supposons qu'il soit nouveau en 2018, il y a aura le 1 comme tu indiques, mais du coup je ne pourrai pas le filtrer sur 2019. Enfin, à la fin, tous mes clients auront un "1" dans le champ en question car ils auront tous été nouveaux à un moment donné.

actuellement j'ai un champ "date de création" (la date complète), "année de création" et "mois de création", le tout dans ma table client. Cette date de création n'est pas la date de création du tiers dans le système, mais la date de la 1ère facture générée pour le tiers en question (on peut parfois créer un tiers à l'avance). Le code de la création de la table est le suivant :

Table_1_commande:
   LOAD TIERS_code,
        min(V_num_fact) as V_num_fact
   FROM [C:\...\Table_TRANSACTION.qvd](qvd)
   Group by TIERS_code;
   
NoConcatenate
tiers_creation:
    LOAD TIERS_code,
         V_num_fact
    Resident Table_1_commande;
 left Join
    LOAD V_num_fact,
         CalendarDate as TIERS_date_creation
    FROM [C:\...\Table_TRANSACTION.qvd](qvd);
DROP Table Table_1_commande;

NoConcatenate
temp_TIERS:
     LOAD num(Accounts_Code) as TIERS_code,
          Accounts_CountryName as TIERS_pays,
          Accounts_Name as TIERS_nom;
     SQL SELECT Accounts_Code, 
                Accounts_CountryName, 
                Accounts_Name
         FROM Accounts;
   Concatenate
     LOAD num(TIERS_code) as TIERS_code,
          client as TIERS_nom
     From [C:\...\Classification clients 03-18.xlsx]
          (ooxml, embedded labels, table is Feuil1)
     Where not Exists (TIERS_code); //rajout de codes client spécifiques
   Left join 
     LOAD num(TIERS_code) as TIERS_code, 
          Cat.1 as TIERS_Cat.1, 
          Cat.2 as TIERS_Cat.2, 
          Cat.3 as TIERS_Cat.3, 
          Cat.4 as TIERS_Cat.4,
          Commercial as TIERS_Commercial
     FROM [C:\...\Classification clients 03-18.xlsx]
          (ooxml, embedded labels, table is Feuil1);

NoConcatenate    
TIERS:
    LOAD num#(TIERS_code) as TIERS_code, 
         TIERS_pays, 
         TIERS_nom, 
         TIERS_Cat.1, 
         TIERS_Cat.2, 
         TIERS_Cat.3, 
         TIERS_Cat.4,
         TIERS_Commercial
    Resident temp_TIERS; 
  Left Join
    LOAD TIERS_code,
         TIERS_date_creation,
         year(TIERS_date_creation) as TIERS_annee_creation,
         month(TIERS_date_creation) as TIERS_mois_creation
    Resident tiers_creation; 
STORE TIERS into TIERS.qvd;
DROP Table tiers_creation;
DROP Table temp_TIERS;

la détermination de la date de création est faite en 2 étapes car sinon j'avais des doublons.

A noter que même s'il y a une facturation à 0, je veux quand même avoir la date de création du tiers en question. En revanche, quand je veux compter les "vrais" clients, je ne veux prendre que ceux à qui on a facturé quelque chose.

Merci !

sylvain_piccarreta · ‎2019-07-26

Bonjour,

as-tu essayé de faire plus simple comme formule ?

C'est à dire, tu mets comme dimensions ce que tu souhaites au niveau clients (pour faire éventuellement tes regroupements)
et tu ajoutes aussi ton champ ANNEE que tu souhaites pour le CA et que tu glisses pour passer en en tête de colonne.

ensuite, tu crées tes expressions :
une pour compter ton CA (sum de ton CA)
une pour ton client (count distinct de ID client) (Si besoin en y ajoutant une condition value>0 si tu as des clients avec du CA à 0 sur l'année en question)

delphinewalter · ‎2019-07-26

Bonsoir,
Non cela ne va pas car je ne peux pas faire de comparaison entre deux années ensuite.
J'ai déjà une condition CA>0 dans mon count distinct de ID client, mais justement elle ne fonctionne pas.
Delphine

sylvain_piccarreta · ‎2019-07-29

au lieu de mettre ta condition dans le if, as-tu essayé de la mettre dans l'expression data set analysis ?

V_CA,HT={">0"}

count({$<TIERS_annee_creation={'$(=max(CalendarYear)-1)'},V_annee={'$(=max(CalendarYear)-1)'}>}distinct TIERS_code)

=count({$<V_CA,HT={">0"},TIERS_annee_creation=.......

pour pouvoir faire des comparaisons entre 2 champs d'une même expression, je te conseille d'utiliser la fonction Before, très pratique. exampe : avg(expr1)-before(avg(expr1). Cela donne par exemple le delta des moyennes pour chaque année.

delphinewalter · ‎2019-07-30

Bonjour Sylvain,

Mettre la condition dans le data set analysis ne change rien : la condition porte sur une somme pour un client donné, et non sur une ligne. En gros c'est si ma facture = 0, alors je ne la compte pas, mais la facture en elle-même est la somme de plusieurs lignes. On m'a dit qu'en sql, je serais avec une requête imbriquée, avec un « having » (un truc du genre « having pour la 1ère facture la somme >0 »)
Je n'ai jamais utilisé la fonction Before, je n'ai pas vraiment compris son fonctionnement.

sylvain_piccarreta · ‎2019-07-31

pour la partie SQL, tout dépend si tu souhaites regrouper ou pas ton CA et avoir aussi toutes les lignes à 0 disponibles dans ton rapport. C'est un peu comme une condition where mais pour les formules lorsque tu utilises la fonction group by

si tu veux faire par SQL, tu peux aussi utiliser la clause group by genre :

select Client, To_char(ta_date,'MM/YYYY), count(distinct client), sum(CA)

from ta_table

group by Client, To_char(ta_date,'MM/YYYY)

having sum(CA)>0

Cela te regroupera par client et ensuite par année, le nombre de client et la somme du CA lorsque la somme de ton CA est supérieure à 0

sylvain_piccarreta · ‎2019-07-31

Before te permet que Qlikview face une comparaison entre la valeur obtenue d'une expression sur une dimension colonne et celle de colonne précédente. L'avantage est que cela est dynamique, c'est-à-dire que l'année prochaine 2020, before l'intégrera automatiquement sans besoin d'ajouter une nouvelle condition.

Exemple:

tu as client comme dimension principale, pour laquelle tu ajoutes une dimension année (2017,2018,2019, ....)

tu as une expression que fait la somme de ton CA par année (25000€ en 2017, 12500€ en 2018, 50000€ en 2019).

En ajoutant une expression sum(CA)-before(sum(CA)), tu auras une nouvelle colonne qui va comparer pour 2018 12500€ (sum(CA)) avec les 25000€ (Before(sum-CA)) donc un résultat de 12500€ de delta.

Pour 2019, il va comparer 50000€(Sum(CA)) avec 12500€ (Before(sum(CA)) donc un résultat de 37500€.

delphinewalter · ‎2019-08-01

Bonjour Sylvain,
C’est justement un truc comme ça que je voudrais faire, mais je ne sais pas comment dans un TCD qlikview dans une expression 😊