Comment calculer une moyenne en YTD et la comparer sur plusieurs années pour un mois donné ?

R: Requête MDX:

 

WITH MEMBER Measures.MyYTD AS SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])

MEMBER Measures.MyMonthCount AS SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))

MEMBER Measures.MyYTDAVG AS Measures.MyYTD /  Measures.MyMonthCount

 

SELECT  {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0,
 [Date].[Calendar].[Month] On 1
FROM [Adventure Works]
WHERE ([Date].[Month of Year].&[7])

 

Resultat

 

  MyYTD MyMonthCount Internet Sales Amount MyYTDAVG
July 2001 $473,388.16 1 $473,388.16 $473,388.16
July 2002 $4,306,075.74 7 $500,365.16 $615,153.68
July 2003 $3,924,170.20 7 $886,668.84 $560,595.74
July 2004 $9,770,899.74 7 $50,840.63 $1,395,842.82

 

Comment calculer les 3 meilleures ventes par année

SELECT

{[Measures].[Internet Sales-Sales Amount]}

On 0,

GENERATE (

[Date].[Calendar Time].[Calendar Year].MEMBERS,

TOPCOUNT (

[Date].[Calendar Year].CURRENTMEMBER

*

[Product].[Model Name].children, 3,[Measures].[Internet Sales-Sales Amount]))

ON 1

FROM [Adventure Works]

 

 

Explication :

La fonction GENERATE (Set1, Set2) renvoie un SET.

Cette fonction effectue une itération pour chaque Tuple de SET1 (chaque année). A chaque itération, GENERATE  execute la fonction TOPCOUNT en restituant un SET basé sur les 3 meilleures ventes de l'année en cours puis passe à la suivante etc ...

 

                               Internet Sales-Sales Amount

CY 2001                Road-150            2 601 402,29

CY 2001                Mountain-100     585 973,27

CY 2001                Road-650            78 998,10

CY 2002                Road-150            2 948 494,48

CY 2002                Road-250            1 571 597,63

CY 2002                Mountain-200     807 308,99

CY 2003                Mountain-200     3 723 270,99

CY 2003                Road-250            2 144 214,15

CY 2003                Touring-1000      1 058 527,08

CY 2004                Mountain-200     3 398 895,27

CY 2004                Touring-1000      1 933 480,77

CY 2004                Road-350-W       1 039 304,89