Somme sur date

**mano_bi** · 28/05/2014, 18h00

Bonjour,

Et avant , merci à ceux qui lirons et me répondrons.

Pour mieux comprendre mon souci/souhait , il faut voir le rapport excel en pj.

En quelque mots ce que je souhaite faire :

Dans la colonne en "Attendu" , j'ai des champs en jaunes.
Ces derniers je les obtiens en fesant la somme des montants jusqu'au jours J + les valeurs N-1 (qui sont au dessus).

Par exemple : Pour la ligne 43 "Réel/Atterrissage indirect" la formule excel est la suivante : =SOMME(E43:AE43;AF42:AI42) .

Je sais pas si j'ai pu être clair mais le rapport est un peu plus parlant.Ainsi c'est le même calcul que je souhaite éffectué.
Pour le jeu de donnée vous pouvez utilisé ce qui est dans le rapport .

Merci à vous

**PhunkyBob** · 29/05/2014, 11h41

Bonjour,

J'ai du mal à faire le lien entre votre description du besoin et le fichier Excel.
"Impression indirecte" veut dire "données de l'année précédente" ?

Quel est votre modèle de données ?

Avec une dimension de date séparée et des "if" dans votre expression, cela ne fonctionne pas ?

**mano_bi** · 29/05/2014, 12h22

Envoyé par PhunkyBob

Bonjour,

J'ai du mal à faire le lien entre votre description du besoin et le fichier Excel.
"Impression indirecte" veut dire "données de l'année précédente" ?

Exactement "Impression indirecte" veut dire "données de l'année précédente".
Comme vous pourrez le constaté toute les lignes en blanc sont remplis pour l'ensemble du mois,justement parce-que ce sont les données de Mai 2013.
Les lignes en blanc ce sont les valeurs de l'année N-1 soit Mai 2013. Pas de souci de calcul à ce niveau.
Les lignes en saumon sont les valeurs de l'année N soit Mai 2014. Et c'est dans les cellules en jaunes que je galère, car c'est là que dois s'afficher le résultat.

exple:
si je cherche le calcul de la ligne "Impression indirecte" [colonne attendu] :
je fais la somme de mes impressions jusqu'au 27 mai = 537535754 + la sum des "impressions" de 2013 (au dessus) c.a.d(28,29,30,31) les 19 024 797 + 18 185 701 + 17 169 761 + 17 307 035. ce qui me donnerais un total de 609 223 048.

donc pour le 28 je refais la somme des impressions de mai 2014 jusqu'au 28 + sum la somme du 29,30 et 31(au dessus) soit 18 185 701 + 17 169 761 + 17 307 035.

pour le 29 je fais sum des impressions jusqu'au 29 + les impressions de 2013 mais du 30 et 31 (au dessus )soit 17 169 761 + 17 307 035.

Pour le 30, je ferais sum des impressions jusqu'au 30 + sum des impressions Mai 2013(au dessus) soit les 17 307 035

Quel est votre modèle de données ?

Pour l'année 2013 ce sont toute les valeurs dans les cellules en blanc.
Pour 2014 ce sont toute celle qui sont dans les cellules en saumon.

Mes 2 tables sont reliées par les dates, et j'ai un calendrier détachée DimDate.
Pour avoir un jeu de donnée ce que je peux faire c'est reprendre les valeurs qui sont déjà dans les cellules , car toute les données sont dedans.

En revanche pour faire leur somme:
Pour les lignes en blanc c'est la somme classique sur les données de 2013. (ça marche)
Pour les lignes en saumon , je fais la somme des valeurs jusqu'au jours J + le reste des valeurs du mois mais en prenant les données de 2013 (donc mai 2013).

Avec une dimension de date séparée et des "if" dans votre expression, cela ne fonctionne pas ?

J'ai essayer avec des if , mais peut être que je m'y prend mal,je tombe pas sur les bonnes valeurs. J'ai justement une dimension date (DimDate) séparée et c'est justement grâce à ce calendrier séparée que j'arrive à récupérer mes données pour les résultats au quotidien.

Mais pour faire des sommes donc pour la colonne Attendu là ça coince.

Merci à vous

**PhunkyBob** · 29/05/2014, 20h13

Avec 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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
Data:
LOAD * INLINE [
Date, Impressions indirect
01/05/2013, 19812335,87
02/05/2013, 19285327,59
03/05/2013, 20266713,82
04/05/2013, 24541590,12
05/05/2013, 24216170,25
06/05/2013, 19949453,32
07/05/2013, 20861471,36
08/05/2013, 21391639,24
09/05/2013, 19965429,4
10/05/2013, 19555107,76
11/05/2013, 21958332,73
12/05/2013, 20101381,23
13/05/2013, 18136156,1
14/05/2013, 19262247,14
15/05/2013, 17714636,9
16/05/2013, 15464214,06
17/05/2013, 16898952,92
18/05/2013, 18851083,65
19/05/2013, 22713787,35
20/05/2013, 18974326,93
21/05/2013, 18403823,57
22/05/2013, 17525190,43
23/05/2013, 16279290,94
24/05/2013, 16612727,19
25/05/2013, 17744303,68
26/05/2013, 17549816,96
27/05/2013, 19198577,56
28/05/2013, 19024796,88
29/05/2013, 18185700,56
30/05/2013, 17169761,1
31/05/2013, 17307035,05
];
 
CONCATENATE (Data)
LOAD * INLINE [
Date, Réel
01/05/2014, 23653383
02/05/2014, 20714933
03/05/2014, 19134430
04/05/2014, 20811157
05/05/2014, 21803919
06/05/2014, 21373400
07/05/2014, 19832878
08/05/2014, 22694025
09/05/2014, 20412354
10/05/2014, 19943862
11/05/2014, 24071704
12/05/2014, 23313113
13/05/2014, 21571425
14/05/2014, 20278438
15/05/2014, 18390450
16/05/2014, 16611911
17/05/2014, 16131854
18/05/2014, 18421159
19/05/2014, 21170255
20/05/2014, 20093474
21/05/2014, 21058857
22/05/2014, 18539832
23/05/2014, 16479148
24/05/2014, 15993963
25/05/2014, 17404190
26/05/2014, 19306643
27/05/2014, 18324997
];

et un calendrier séparé :

Code :

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

1
2
3
4
DimDate:
LOAD 
	date(Recno() + makedate(2014, 5, 1) - 1) AS DimDate
AUTOGENERATE 31;

Je fais un graphique de type "tableau droit".
Je mets "DimDate" en dimension.
En expression 1, je mets :

Code :

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

sum(if(DimDate = Date, Réel))

ce qui donne les valeurs de l'année courante.

En expression 2, je mets :

Code :

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

sum(if(AddMonths(DimDate, -12) = Date, [Impressions indirect]))

ce qui donne les valeurs de l'année précédente.

En expression 3, je mets :

Code :

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

1
2
3
4
5
if(
count(if(DimDate = Date, Réel)) > 0,
sum(if(DimDate = Date, Réel)),
sum(if(AddMonths(DimDate, -12) = Date, [Impressions indirect]))
)

et je coche "Somme" dans la partie "Totaux".
Cette dernière expression regardera s'il y a un chiffre pour l'année coutante, et si ce n'est pas le cas, prendra le chiffre de l'année précédente.

S'il y a besoin de mettre l'expression dans un tableau croisé et que le total est en mode "sous-total", cette expression ne sera pas bonne, car le sous-total sera "la même expression calculée au niveau supérieur", ce qui fait que ça prendra en compte les valeurs de l'année courante et les valeurs de l'année précédente.
Il faut mettre l'expression

Code :

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

1
2
3
4
5
6
sum(aggr(
if(
count(if(DimDate = Date, Réel)) > 0,
sum(if(DimDate = Date, Réel)),
sum(if(AddMonths(DimDate, -12) = Date, [Impressions indirect]))
), DimDate))

qui fera la même chose, mais pré-calculera les valeurs pour chaque date avant d'en faire la somme.

**mano_bi** · 30/05/2014, 12h40

Merci PhunkyBob,

ça marche, mais j'ai un petit intrus :

Dans le champ de cette formule, le 01/05/2014 j'ai un 23.236.605 qui apparait et le nombre d'impression du 31 mai 2013 soit 17 037 305 n'apparait, en faite que je chasse la valeur du 1 er mai 2013 (cf tableau) pour que celle du 31 mai 2013 soit intégré.

ce qui donne les valeurs de l'année courante.

En expression 2, je mets :

Code :

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

sum(IF(AddMonths(DimDate, -12) = Date, [Impressions indirect]))

J'ai mis le tableau en pj, bien entendu il y a eu des mise à jours au niveau des données.

Merci

**PhunkyBob** · 30/05/2014, 13h23

Désolé, je ne comprends pas votre problème.

**mano_bi** · 30/05/2014, 13h55

Envoyé par PhunkyBob

Désolé, je ne comprends pas votre problème.

En fait j'ai dans la 2eme colonne ( données de l'année N-1 : encadré en rouge) , la valeur du 1er mai(19 812 336) apparaît dans la ligne du 2 mai, et cellle du 2 mai dans la ligne du 3 mai etc.. ce qui fait que la valeur du 30 mai apparaît dans la ligne du 31 mai et du coup la valeur du 31 mai n'est pas inclus.

en pj j'ai mis le tableau fait avec encadré mon souci.

**PhunkyBob** · 30/05/2014, 14h51

C'est bizarre, parce qu'en faisant un copier / coller du code que je vous ai fourni, j'obtiens bien "19 812 336" en face de "1 mai".

Quelle version de QlikView utilisez-vous ?

Si vous rajoutez une expression

Code :

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

AddMonths(DimDate, -12)

qu'affiche-t-elle en face de "01/05/2014" ?

**mano_bi** · 30/05/2014, 15h04

Envoyé par PhunkyBob

C'est bizarre, parce qu'en faisant un copier / coller du code que je vous ai fourni, j'obtiens bien "19 812 336" en face de "1 mai".

Quelle version de QlikView utilisez-vous ?

Si vous rajoutez une expression

Code :

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

AddMonths(DimDate, -12)

qu'affiche-t-elle en face de "01/05/2014" ?

Je comprend pas moi même pourtant les autres formules marche. Je copie /colle votre formule en adaptant à mon résultat final j'obtiens une autre valeur différente à la date.
du en cherchant une solution quand je fais :

Code :

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

1
2
3
sum(((IF(DimDate - 364 = Dates, [Pages Vues/Jours]))*(1+0.14284))*(1+0.03))*3.14
*
0.73

j'ai bien chaque valeur à coté de sa date, mais comme on est le 30, la valeur du 31 Mai n’apparaît.

j'utilise une version open source Version 11.20.12235.0 SR5 64-bit Edition

svp ne tenez pas compte des *(1+0.14284))*(1+0.03))*3.14.

Si vous rajoutez une expression

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

AddMonths(DimDate, -12)

qu'affiche-t-elle en face de "01/05/2014" ?

le 1 er mai toujours les "23.236.605" au lieu des "19.812.336"

merci

**PhunkyBob** · 30/05/2014, 15h27

Si vous mettez

Code :

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

DimDate - 364 = Dates

c'est normal que ça soit décalé !

le "-364", c'était (dans un autre post) pour faire en sorte que ça prenne le même jour de la semaine de l'année précédente (lundi vs. lundi, etc...).
Donc forcément, à coté du "jeudi 1er mai 2014", vous avez le chiffre du "jeudi 2 mai 2013".

Si vous faites

Code :

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

AddMonths(DimDate, -12) = Dates

, alors vous aurez à coté du "jeudi 1er mai 2014" les chiffres du "mercredi 1er mai 2013".

**mano_bi** · 30/05/2014, 16h38

Envoyé par PhunkyBob

Si vous mettez

Code :

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

DimDate - 364 = Dates

c'est normal que ça soit décalé !

le "-364", c'était (dans un autre post) pour faire en sorte que ça prenne le même jour de la semaine de l'année précédente (lundi vs. lundi, etc...).
Donc forcément, à coté du "jeudi 1er mai 2014", vous avez le chiffre du "jeudi 2 mai 2013".

Si vous faites

Code :

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

AddMonths(DimDate, -12) = Dates

, alors vous aurez à coté du "jeudi 1er mai 2014" les chiffres du "mercredi 1er mai 2013".

ah ok, j'applique votre formule mais j'ai toujours les "23.653.605" et ils ne correspondent pas au 1 er mai 2013... ( rapport exemple excel).
Comme dans votre tableau vous avez les "23.653.605" le 1er mai. Il y a que sur cette colonne que ca coince et je comprend pas.

merci

**PhunkyBob** · 31/05/2014, 12h22

Envoyé par mano_bi

ah ok, j'applique votre formule mais j'ai toujours les "23.653.605" et ils ne correspondent pas au 1 er mai 2013... ( rapport exemple excel).
Comme dans votre tableau vous avez les "23.653.605" le 1er mai. Il y a que sur cette colonne que ca coince et je comprend pas.

Je ne vois pas pourquoi vous voulez le 1er mai 2013 et pas le 1er mai 2014.
Je croyais que le but était de prendre les données de l'année courante quand elles étaient disponibles et les données de l'année précédente sinon.
Il y a des données pour le 1er mai 2014, donc c'est celles là qu'on prend, non ?
C'est d'ailleurs cette valeur qui est prise en compte dans votre fichier Excel...

**mano_bi** · 31/05/2014, 22h37

Si , mais en gros j'aimerais obtenir le même tableau que vous avez les mêmes chiffres.
Or j'ai les mêmes chiffres que vous excepté la deuxième colonne en partant de la gauche.
En gros dans l'expression 2 qui à la formule ci-dessous

Code :

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

sum(IF(AddMonths(DimDate, -12) = Date, [Impressions indirect]))

j'ai pas les mêmes chiffres que vous.
Pourtant on à mes mêmes données...

je sais pas si vous voyez ce que je veux dire.

**PhunkyBob** · 31/05/2014, 23h40

Envoyé par mano_bi

je sais pas si vous voyez ce que je veux dire.

Non, je ne vois pas.

Si on a les mêmes données et les mêmes expressions et la même version de QlikView, je ne vois pas pourquoi on aurait un résultat différent.

Surtout si

Code :

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

AddMonths(DimDate, -12)

donne la date attendue.

Je vous conseille de vérifier vos chiffres. Peut être y a-t-il un problème dans votre chargement...

**mano_bi** · 02/06/2014, 17h06

Envoyé par PhunkyBob

Non, je ne vois pas.

Si on a les mêmes données et les mêmes expressions et la même version de QlikView, je ne vois pas pourquoi on aurait un résultat différent.

Surtout si

Code :

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

AddMonths(DimDate, -12)

donne la date attendue.

Je vous conseille de vérifier vos chiffres. Peut être y a-t-il un problème dans votre chargement...

Je dit la même chose aussi.
je vais revoir mes données.

merci

**mano_bi** · 02/07/2014, 15h22

Je me permet de ré ouvrir un sujet résolus, car après vérification, je retombe sur le même problème.
Et là je suis sur un bon exemple car je suis au début du mois.

Pour les données issues de juillet 2014 pas de souci,
celle issue de Juillet 2013 pas de souci.

cependant quand il faut sommé les deux Là est le souci, j'ai un décalage de nombre.
Le 1 er juillet 2014 est un mardi ,
le 1er juillet 2014 est lundi, or c'est pas la donnée du lundi (1er juillet) qu'on prend mais celle du 2 juillet Mardi car on compare en fonction du jours.

le 2/07/14 tombe un Mercredi, et le 02/07/13 tombe un mardi , donc ce donnée Mercredi + donnée Mardi.

J'utilise cette formule qui m'a été gentille-ment donnée et expliquer par un membre PhunkyBob.
en pj mon tcd qui pourras illustré mon problème.
Le bon total pour la colonne atterrissage est 301.204.981 et non 308.216.504 comme affiché

Code :

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

1
2
3
4
5
6
sum(Aggr(
IF(
count(IF(Dates = DimDate, [Pages Vues/Jours])) > 0,
sum(IF(Dates = DimDate, [Pages Vues/Jours])),
sum(IF(AddMonths( DimDate, -12) = Dates, [Pages Vues/Jours])))*(1+0.14284)*(1+(0.04)),
DimDate))

**PhunkyBob** · 02/07/2014, 15h45

L'expression que vous donnez est celle utilisée pour "atterrissage" ?

Si oui, quelle est la formule pour "Année 2014" ? Et pour "Année précédente" ?
Parce qu'on devrait trouver la valeur de "Année 2014" en face de "01/07/2014" et la valeur de "Année précédente" pour les autres lignes...
Il n'y a pas de raison que tout soit décalé de 1 ligne.

Que se passe-t-il si vous agrégez en plus par "Dates" ?

Code :

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

1
2
3
4
5
6
sum(Aggr(
IF(
count(IF(Dates = DimDate, [Pages Vues/Jours])) > 0,
sum(IF(Dates = DimDate, [Pages Vues/Jours])),
sum(IF(AddMonths( DimDate, -12) = Dates, [Pages Vues/Jours])))*(1+0.14284)*(1+(0.04)),
DimDate, Dates))

**mano_bi** · 02/07/2014, 15h51

Envoyé par PhunkyBob

L'expression que vous donnez est celle utilisée pour "atterrissage" ?

oui.

Si oui, quelle est la formule pour "Année 2014" ? Et pour "Année précédente" ?

formule pour "Année 2014" ? :

Code :

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

sum(IF(Dates = DimDate,IF(Dates = DimDate, [Pages Vues/Jours])))

"Année précédente" :

Code :

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

sum((IF(Dates = DimDate - 364,[Pages Vues/Jours])))*(1+0.14284)*(1+(0.04))

Parce qu'on devrait trouver la valeur de "Année 2014" en face de "01/07/2014" et la valeur de "Année précédente" pour les autres lignes...
Il n'y a pas de raison que tout soit décalé de 1 ligne.

Que se passe-t-il si vous agrégez en plus par "Dates" ?

Code :

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

1
2
3
4
5
6
sum(Aggr(
IF(
count(IF(Dates = DimDate, [Pages Vues/Jours])) > 0,
sum(IF(Dates = DimDate, [Pages Vues/Jours])),
sum(IF(AddMonths( DimDate, -12) = Dates, [Pages Vues/Jours])))*(1+0.14284)*(1+(0.04)),
DimDate, Dates))

de 12.424.700 en passe à 26.396.776

**mano_bi** · 02/07/2014, 15h57

Parce qu'on devrait trouver la valeur de "Année 2014" en face de "01/07/2014" et la valeur de "Année précédente" pour les autres lignes...
Il n'y a pas de raison que tout soit décalé de 1 ligne.

dans les champs Année 2014 et Année précédente on à bien les bonnes données à côtés de sa date.

Cependant dans atterrissage non, sachant que le principe de calcul dans le champs atterrissage est :

je prend la donnée du jours soit 10.453.630 + les données du mois de juillet 2013 exclus les 13.131.988 car même date.
donc ça donne :
10.453.630 + (15.890.786 + 11.654.155 + ...+ 8.816.545 + 8.091.534 ).
demain on fera 10.453.630 + données du 2 juillet + (11.654.155 + ...+ 8.816.545 + 8.091.534)

On exclus la donnée du jours j et jours passé dans la somme .

je sais pas si j'ai pu apporté plus de précision.

merci

**PhunkyBob** · 02/07/2014, 16h00

Je ne comprends pas pourquoi vous utilisez dans une formule

Code :

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

sum((IF(Dates = DimDate - 364,[Pages Vues/Jours]))

qui donne la valeur du même jour de la semaine de l'année précédente, et dans l'autre formule

Code :

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

sum((IF(Dates = addmonths(DimDate, -12),[Pages Vues/Jours]))

qui donne la valeur du même jour du mois de l'année précédente.

C'est donc logique que vous ayez un décalage si vous n'utilisez pas la même formule.

Somme sur date

QlikView

Discussions similaires

Partager

Partager