Et eksempel på hvordan man kan beregne pct. af totalsalg og pct af salget på niveauet over det aktuelle.
WITH
MEMBER [Measures].[Pct of total] AS ‘SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) /SUM( {[Product].DefaultMember}, [Measures].[Unit Sales])’, FORMAT_STRING=’#0.00%’
MEMBER [Measures].[Pct of parent total] AS ‘IIF( [Product].Parent <> NULL, SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) /SUM( {[Product].Parent.Children}, [Measures].[Unit Sales]), 1)’, FORMAT_STRING=’#0.00%’
SELECT
{ [Measures].[Unit Sales], [Measures].[Pct of [...]
Posts Tagged ‘MDX’
30 dec
Pct af totalen
17 okt
Kumulative værdier
Her ses et eksempel på hvordan man beregner kumlative værdier over en periode
WITH
MEMBER MEASURES.[Sales To Date] as ‘
SUM( {[Time].CurrentMember.Level.Members}.Item(0)
: Time.CurrentMember,[Measures].[Sales Count])’
MEMBER MEASURES.[Sales Year To Date] AS ‘
SUM(YTD([Time].CurrentMember),[Measures].[Sales Count])’
MEMBER MEASURES.[Sales Quarter To Date] as ‘
SUM(QTD([Time].CurrentMember),[Measures].[Sales Count])’
SELECT
{[Measures].[Sales Count],
[Measures].[Sales To Date],
[Measures].[Sales Quarter To Date],
[Measures].[Sales Year To Date]} ON ROWS,
DESCENDANTS([Time], , LEAVES) ON COLUMNS
FROM
[Sales]
11 okt
Pct af total
Dette MDX eksempel viser hvordan man kan beregne den aktuelle pct af total salget, og pct af total salget for niveauet over det aktuelle.
WITH
MEMBER [Measures].[Pct of total] AS ‘
SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) /
SUM( {[Product].DefaultMember}, [Measures].[Unit Sales])’,
FORMAT_STRING=’#0.00%’
MEMBER [Measures].[Pct of [...]
10 okt
Den absolutte værdi
Her er et eksempel på hvordan man beregner den absolutte værdi af et givent Measure.
Code Block
WITH
MEMBER [Measures].[Neg Profit] AS ‘-1 * [Measures].[Profit]‘
MEMBER [Measures].[Abs Profit] AS ‘ABS([Measures].[Neg Profit])’
SELECT
{[Measures].[Neg Profit], [Measures].[Abs Profit]} ON COLUMNS,
NON EMPTY DESCENDANTS( [Customers] ) ON ROWS
FROM
[Sales]
9 okt
Distinct count on several dimension members
Here is an example on how you can select the distinct count on a combination of product categories.
The example is written for FoodMart 2000
WITH
SET [Categories] AS ‘
{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine]}
+
{[Product].[All Products].[Drink].[Beverages].[Carbonated Beverages]}’
MEMBER [Measures].[Count] AS ‘SUM( {[Categories]}, [Measures].[Sales Count] )’
MEMBER [Measures].[Count A] AS ‘SUM(
{[Product].[All Products].[Drink].[Alcoholic Beverages].[Beer and Wine]},
[Measures].[Sales Count] )’
MEMBER [Measures].[Count B] [...]
30 jan
Mosha, manden med lyset…
I dag faldt jeg tilfældigt over en ret så, for mig, brugbar information. Længe har jeg lavet MDX statements, længe har jeg brugt IIF-statements og længe har jeg været irriteret over ikke at kunne returnere ‘N/A’ ved ukendte/ikke-definerede værdier. Det kan man så alligevel. Som skrevet var det lidt tilfældigt, at jeg spottede notationen i [...]
30 aug
Rank that thing…
Ranking i MDX kan være en spøjs ting at arbejde med.
Her den anden dag sad jeg med problemet at skulle ranke et Crossjoin set hvor jeg kun ville have .children. Det viste sig, at når man rankede, skulle man benytte sig af Extract( [Dimension].Members, [Dimension].DefaultMember ) istedet for [Dimension].Children
I min verden er det en og [...]
