Summary row with different aggregate function for each column
Today my customer ask me a question about how to write the MDX with a summary row however this row with different aggregate functions for each column.
We know in DeepSee analyzer has pivot table option "Summary", user can select sum, avg ... aggregate functions to get the summary row/column. However we can not specify different aggregate function for each measure column.
Here show the example to use All level and IIF function achieve that. see the example (Holefood cube in Sample namespace) below
WITH MEMBER measures.[with avg summary info] AS
'IIF(Product.CurrentMember.Properties("LEVEL_NUMBER") = 1,
AVG(Product.[Product Category].Members, measures.[amount sold]),
SUM(Product.[Product Category].Members, measures.[amount sold]))'
MEMBER measures.[total count] AS 'SUM(Product.[Product Category].Members)'
SELECT {Product.[Product Category].Members, [Product].[All Product]} ON 1,
{measures.[with avg summary info],measures.[total count]} ON 0
FROM Holefoods
with avg summary i total count
1 Candy 149,187.22 83,833
2 Cereal 513,770.68 83,852
3 Dairy 518,826.37 56,481
4 Fruit 744,168.60 85,819
5 Pasta 1,940,939.62 273,971
6 Seafood 2,655,134.41 75,355
7 Snack 3,111,815.51 267,996
8 Vegetable 884,450.97 110,844
9 All Products 1,314,786.67 1,038,151
============================================
Cheng Cheng
HI, Cheng!
Thanks, great example!
What is the "LEVEL_NUMBER"? Is it a kind of internal property?
Hi Evgeny
level_number is one of intrinsic member properties compared with user defined properties like UnitPrice. It is the distance of the member from the root of the hierarchy. The root level is zero.
I tried to find out some Cache defined MDX intrinsic member properties here (Maybe document team can give a full list)
with member measures.Parentlevel As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("LEVEL_NUMBER")' member measures.ParentID As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("ID")' member measures.ParentKey As '[Product].P1.[Product Name].CurrentMember.Parent.Properties("KEY")' member measures.Price As '[Product].P1.[Product Name].CurrentMember.Properties("UnitPrice")' select [product].[product name].members on 1, {measures.parentlevel,measures.parentid, measures.Parentkey, measures.Price} on 0 from holefoods
Parentlevel ParentID ParentKey Price
1 Bagels (dozen) 2 4 Snack 2.95
2 Bundt Cake 2 4 Snack 19.95
3 Calamari (frozen 2 6 Seafood 22.95
4 Cheerios (box) 2 1 Cereal 3.95
5 Donuts (dozen) 2 4 Snack 2.95
6 Free-range Donut 2 4 Snack 12.95
7 Fruit Loops (box 2 8 Fruit 4.95
8 Lifesavers (roll 2 3 Candy 1.15
9 Onion ring 2 7 Vegetable 4.95
10 Onion ring 2 7 Vegetable 5.95
11 Penne (box) 2 5 Pasta 1.95
12 Pineapple Rings 2 8 Fruit 8.95
13 Pretzels (bag) 2 4 Snack 3.95
14 Swiss Cheese (sl 2 2 Dairy 5.95
15 Tortellini (froz 2 5 Pasta 6.95
16 Unsalted Pretzel 2 4 Snack 4.25
17 Ziti (box) 2 5 Pasta 1.95
---------------------------------------------------------------------------
Hi, Cheng!
Thank you for the explanation! Great use case, btw. Now it's more clear with your initial post.
It would be great if you include Level_number in the query too to see what rows has different Level_number.
Like this:
I suppose 'All Product' level should have the Sum of all Product levels. But in your example 'All product' lesser than 'Snack' for example.
Sorry, Cheng Cheng!
Got the idea now: you wanted Sum function for each Product Category, and Avg function for All Products in the same pivot)
And this use case of IIF and LEVEL_NUMBER solves the task.
Great! Thanks!
Here's the list of all intrinsic member properties:
Hi Cheng Cheng.
This doc lists all intrinsic properties:
http://docs.intersystems.com/cache20161/csp/docbook/DocBook.UI.Page.cls?...
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue