Shulou(Shulou.com)06/03 Report--

The following content has been fully tested, combined with other articles seen online, revised and collated.

The test environment is as follows:

Windows 2003 sp2

Mondrian-3.1.1.12687

Jdk1.6

Tomcat 5.5.23

LISTING 1: Determining Products Sold in Each State

Description: products that are sold in each state (province)

[en]

With set [SoldInUSA] as' Filter ([Product]. [Brand Name] .Members, Not IsEmpty (([USA], [Unit Sales])'

Member [Measures]. [SoldInState] as' iif (IsEmpty (([Product] .CurrentMember, [Unit Sales], [Customers] .CurrentMember)), "No", "Yes")'

Select [USA]. Children on COLUMNS

[SoldInUSA] on ROWS

From Sales

Where ([Measures]. [SoldInState])

[cn]

With set [SoldInUSA] as' Filter ([Product]. [Brand Name] .Members, Not IsEmpty (([USA], [Unit Sales])'

Member [Measures]. [SoldInState] as' iif (IsEmpty (([Product] .CurrentMember, [Unit Sales], [Customers] .CurrentMember)), "No", "Yes")'

Select [USA] .children on COLUMNS

[SoldInUSA] on ROWS

From Sales

Where ([Measures]. [SoldInState])

LISTING 2: Determining Top 10 Product Categories

Description: list the top ten product categories by sales

Select {[Measures]. [Unit Sales]} ON COLUMNS

TopCount ([Product]. [Product Category] .Members, 10.0, [Measures]. [Unit Sales]) ON ROWS

From [Sales]

Listing_03.Determining Brands Sold During the Past Three Quarters.txt

Description: list the sales records of goods that have been sold in the past three quarters

[en]

With set [LastQuarter] as' Tail (Filter ([Time]. [Quarter] .Members, Not IsEmpty ([Time] .CurrentMember), 1)'

Set [Last3Quarters] as'[LastQuarter] .item (0) .item (0) .Lag (2): [LastQuarter] .item (0) .item (0)'

Select [Last3Quarters] on COLUMNS

Non Empty Union (Descendants ([Food], [Product]. [Brand Name]), Descendants ([Drink], [Product]. [Brand Name]) on ROWS

From Sales

[cn]

With set [LastQuarter] as' Tail (Filter ([Time]. [Quarter] .Members, Not IsEmpty ([Time] .CurrentMember), 1)'

Set [Last3Quarters] as'[LastQuarter] .item (0) .item (0) .Lag (2): [LastQuarter] .item (0) .item (0)'

Select [Last3Quarters] on COLUMNS

Non Empty Union (Descendants ([food], [Product]. [Brand Name]), Descendants ([drinks], [Product]. [Brand Name])) on ROWS

From Sales

Listing_04.Determining Recent Trends for Best-Selling Brands.txt

Description: find out the top 10 goods and sales with the best sales trend in the last 6 months

With set [TenBest] as' TopCount ([Product]. [Brand Name] .Members, 10, [Unit Sales])'

Set [LastMonth] as' Tail (Filter ([Time]. [Month] .Members, Not IsEmpty ([Time] .CurrentMember), 1)'

Set [Last6Months] as'[LastMonth] .item (0) .item (0) .Lag (6): [LastMonth] .item (0) .item (0)'

Select [Last6Months] on COLUMNS

[TenBest] on ROWS

From Sales

Listing_05.Determining Brands that Make Up 80 Percent of Sales.txt

Description: find out the sales and records of the goods that make up 80% of sales.

Select {[Unit Sales]} on COLUMNS

TopPercent ([Product]. [Brand Name] .members, 80, [Unit Sales]) on ROWS

From Sales

Listing_06.Determining Brands That Make Up the Bottom 20 Percent of Sales.txt

Description: sort by sales volume and find out the sales records of goods that make up 20% of sales volume.

Select {[Unit Sales]} on COLUMNS

Non Empty BottomPercent ([Product]. [Brand Name] .members, 20, [Unit Sales]) on ROWS

From Sales

Listing_07.Determining the Top Five Stores and the Top Five Customers.txt

Description: find out the top 5 stores with the best sales and the top 5 customers of each store and their sales records

Select {[Unit Sales]} on COLUMNS

Generate (TopCount ([Store]. [Store Name] .members, 5, [Unit Sales])

{[Store] .CurrentMember} * TopCount ([Customers]. [Name] .Members, 5, ([Unit Sales])

[Store] .CurrentMember) on ROWS

From Sales

Listing_08.Determining Two Top-Selling Products.txt

Description: find out the sales records of the top 2 subcategory models in each product category, as well as the percentage of subcategory models in the large category.

With member [Measures]. [PercTotalSales] as

'Sum (TopCount ([Product] .CurrentMember.Children, 2, [Unit Sales]), [Unit Sales])

/ ([Product] .CurrentMember, [Unit Sales])'

FORMAT_STRING ='# #. 0%'

Select [Store]. [(All)] .Members on COLUMNS

Generate ([Product]. [Brand Name]. Members

Union (

TopCount ([Product] .CurrentMember.Children, 2, [Unit Sales]) * {[Unit Sales]}

{([Product] .CurrentMember, [Measures]. [PercTotalSales])}

)

) on ROWS

From Sales

= =

Listing_09.Highlighting Products in the Bottom 10 Percent.txt

Description: find out the last 10% of product sales in each of the 4 quarters, and show it in bold

With set [LastQuarter] as' Tail (Filter ([Time]. [Quarter] .Members, Not

IsEmpty ([Time] .CurrentMember))'

Set [Last4Quarters] as'[LastQuarter] .item (0) .item (0) .Lag (3): [LastQuarter] .item (0) .item (0)'

Member [Measures]. [HLUnit Sales] as'[Unit Sales]'

FONT_FLAGS = 'iif (Count (

Intersect (BottomPercent ([Product]. [Brand Name] .members, 10, ([Unit Sales]))

{[Product] .CurrentMember})

) = 0,0,1)'

Select [Last4Quarters] on COLUMNS

[Product]. [Brand Name]. Members on ROWS

From Sales

Where ([Measures]. [HLUnit Sales])

Cell properties VALUE, FORMATTED_VALUE, FONT_FLAGS

Listing_10.Comparing Sales with Those of Parallel Months.txt

Description: compare sales at points in time with the same relative position, such as July this year and July last year

With set [PromoMonths] as' Filter ([Time]. [Month] .Members, Not IsEmpty (([Unit Sales], [Double Your Savings])))'

Set [PromoRange] as' Head ([PromoMonths]). Item (0) .item (0): Tail ([PromoMonths]). Item (0) .item (0)'

Member [Measures]. [Uplift] as'([Unit Sales], [Double Your Savings])'

Member [Measures]. [This Quarter] as'[Unit Sales]'

Member [Measures]. [Last Quarter] as'(ParallelPeriod ([Time]. [Quarter]), [Unit Sales])'

Member [Measures]. [Growth] as'[Measures]. [This Quarter]-[Measures]. [Last Quarter]'

Select [PromoRange] on Columns

{[Measures]. [This Quarter], [Measures]. [Last Quarter], [Measures]. [Growth], [Measures]. [Uplift]} on Rows

From [Sales]

Listing_11.Determining Sales That Exceed Store Cost by 160 Percent.txt

Description: find out the products and sales records with a profit margin of more than 16%

With member [Measures]. [SalesRatio] as'([Store Sales]-[Store Cost]) / [Store Cost]'

FORMAT_STRING ='# #%'

Select {[Store Sales], [Store Cost], [Measures]. [SalesRatio]} on COLUMNS

Filter ([Product]. [Brand Name] .Members, [Measures]. [SalesRatio] > 1.60) on ROWS

From Sales

Listing_12.Determining Brands that Have Grown by More Than 50 Percent.txt

Description: find out the record of product sales with a sales growth rate of more than 50% in the most recent quarter compared with the previous quarter.

With set [LastQuarter] as' Tail (Filter ([Time]. [Quarter] .Members, Not

IsEmpty ([Time] .CurrentMember))'

Member [Measures]. [CurrQSales] as'([LastQuarter] .item (0) .item (0), [Unit Sales])'

Member [Measures]. [PrevQSales] as'([LastQuarter] .item (0) .item (0) .PrevMember, [Unit Sales])'

Member [Measures]. [Growth] as'([Measures]. [CurrQSales]-[Measures]. [PrevQSales]) / [Measures]. [PrevQSales]'

FORMAT_STRING='##%'

Select {[Measures]. [PrevQSales], [Measures]. [CurrQSales], [Measures]. [Growth]} on COLUMNS

Filter ([Product]. [Brand Name] .Members, [Measures]. [Growth] > 0.5) on ROWS

From Sales

Listing_13.Determing the Top 10 and Bottom 10 Product Brands.txt

Description: find out the sales records of the top 10 and bottom 10 products, and list the total ranking, that is, to find out the best and worst products

With set [OrderedBrands] as' Order ([Product]. [Brand Name] .Members, [Unit Sales], BDESC)'

Member [Measures]. [Brand Rank] as' Rank ([Product] .CurrentMember, [OrderedBrands])'

Select {[Measures]. [Brand Rank], [Unit Sales]} on COLUMNS

Union (Head ([OrderedBrands], 10), Tail ([OrderedBrands], 10)) on ROWS

From Sales

Listing_14.Comparing Product Trends.txt

Description: it is useless to compare the sales trend of the product

With set [LastQuarter] as' Tail (Filter ([Time]. [Quarter] .Members, Not

IsEmpty ([Time] .CurrentMember))'

Set [Last4Quarters] as'[LastQuarter] .item (0) .item (0) .Lag (3): [LastQuarter] .item (0) .item (0)'

Member [Measures]. [GroupAvg] as' Avg ([Product] .CurrentMember.Siblings, [Unit Sales])'

Member [Measures]. [AllAvg] as' Avg ([Product]. [Product Name] .members, [Unit Sales])'

Select [Last4Quarters] on COLUMNS

{[Unit Sales], [Measures]. [GroupAvg], [Measures]. [AllAvg]} on ROWS

From Sales

/ * where ([Ebony Plums]) * /

Listing_15.Determining the Top 10 Middle-Tier Brands.txt

Description: find out the sales records of the top 10 products under certain conditions, such as those with sales between 500 and 3000

With set [LastQuarter] as' Tail (Filter ([Time]. [Quarter] .Members, Not

IsEmpty ([Time] .CurrentMember))'

Set [Last4Quarters] as'[LastQuarter] .item (0) .item (0) .Lag (3): [LastQuarter] .item (0) .item (0)'

Member [Measures]. [GroupAvg] as' Avg ([Product] .CurrentMember.Siblings, [Unit Sales])'

Member [Measures]. [AllAvg] as' Avg ([Product]. [Product Name] .members, [Unit Sales])'

Member [measures]. [abc] as'[Product] .CurrentMember.uniformame'

Select [Last4Quarters] on COLUMNS

{[Unit Sales], [measures]. [GroupAvg], [measures]. [AllAvg], [measures]. [abc]} on ROWS

From Sales

/ * where ([Ebony Plums]) * /

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.