- Member percentage Analysis
Functions: currentmember and parent;
Analyze the total sales percentage of all cities in each city.
With member measures. [unit sales percent] as '(store. currentmember, measures. [unit sales])/(store. currentmember. parent, measures. [unit sales]) ', format_string = 'percent'
Select {measures. [unit sales], measures. [unit sales percent]} on columns,
Order (descendants (store .[ USA ]. [Ca], store .[ Store City ], Self), [measures]. [unit sales], ASC) on rows
From sales
- Key customer Distribution Analysis
Functions: Count, sum, filter, descendants, etc;
The number of important customers in each province and their total purchases are analyzed. The "important customer" is defined as a customer's purchase amount or number of purchases reaching or exceeding a certain value.
With member [measures]. [qualified count] As 'count (filter (descendants (MERS MERs. currentmember, [MERs]. [name]), ([measures]. [store sales])> 10000 or ([measures]. [unit sales])> 10 ))'
Member [measures]. [qualified sales] As 'sum (filter (descendants (MERS MERs. currentmember, [MERs]. [name]), ([measures]. [store sales])> 10000 or ([measures]. [unit sales])> 10), ([measures]. [store sales])'
Select {[measures]. [qualified count], [measures]. [qualified sales]} on columns,
Descendants ([MERs]. [all customers], [State Province], self_and_before) on rows
From sales
- Sort
Function: Order
Sort each product category in descending order by store sales indicators, which are classified into intra-dimension Sorting/overall sorting.
Select {[measures]. [unit sales], [measures]. [store sales]} on columns,
Order ([product]. [product department]. members, [measures]. [store sales], DESC) on rows
From sales
- Historical cumulative value
Functions: YTD, sum, and descendants
Calculate the current year's cumulative value of sales YTD (). Similarly, you can also solve the historical cumulative YTD (), the cumulative MTD () of this month, and the cumulative WTD () of this week, and more general functions periodtodate ().
With member [measures]. [accumulated sales] As 'sum (YTD (), [measures]. [store sales])'
Select {[measures]. [store sales], [measures]. [accumulated sales]} on columns, {descendants ([time]. [2, 1997], [time]. [month])} on rows
From [warehouse and sales]
- Arithmetic Operation
Function: Four arithmetic functions;
You can perform four arithmetic operations on members and indicators to dynamically derive new members and indicators.
With member measures. profitpercent as '([measures]. [store sales]-[measures]. [store cost])/([measures]. [store cost]) ', format_string = '#. 00%'
Member [time]. [first half 97] as '[time]. [1997]. [Q1] + [time]. [1997]. [Q2]'
Member [time]. [second half 97] as '[time]. [1997]. [Q3] + [time]. [1997]. [Q4]'
Select {[time]. [first half 97], [time]. [second half 97], [time]. [1997]. Children} on columns,
{[Store]. [store country]. [USA]. Children} on rows
From [sales]
Where (Measures. profitpercent)
- Logical judgment
Function: IIF
Logical judgment can produce different results based on different conditions. The following example checks whether each store is a big seller of beer and liquor.
With member [product]. [bigseller] As 'iif ([product]. [Drink]. [alcoholic beverages]. [beer and wine]> 100, "yes", "no ")'
Select {[product]. [bigseller], [product]. Children} on columns,
{[Store]. [All stores]. [ USA ]. [Ca]. Children} on rows
From sales
- Member attributes
Functions: Properties and dimension Properties
Member attributes are bound to Members, which makes it difficult to select an appropriate method of use. The following is an example of using the member attributes. Each store member lists the store type attributes. The attributes such as the store manager, store size, and store address can also be listed. This method can be used with a little flexibility to solve the problem of displaying the company name> enterprise code in the past.
With member [measures]. [storetype] as '[store]. currentmember. properties ("store type ")',
Member [measures]. [profitpct] as '(measures. [store sales]-measures. [store cost])/measures. [store sales] ', format_string = '##. 00%'
Select {descendants ([store]. [USA], [store]. [store name])} on columns,
{[Measures]. [store sales], [measures]. [store cost], [measures]. [storetype], [measures]. [profitpct]} on Rows"
From sales
Another usage:
Select {[measures]. [units shipped], [measures]. [units ordered]} on columns,
[Store]. [store name]. Members dimension properties [store]. [store name]. [store type] on rows
From warehouse
- Complex logic for multi-step computing
Function: can be a logical combination of any function.
Find customers who have never bought dairy products. The solution first calculates the total number of dairy products purchased by each customer in the past, and then finds out the customers whose cumulative value is 0. Similarly, in the past, the problem that the tax amount is greater than the average tax amount can be solved similarly.
With member [measures]. [dairy ever] As 'sum ([time]. members, ([measures]. [unit sales], [product]. [food]. [dairy])'
Set [MERs mers who never bought dairy] As 'filter ([Customers]. members, [measures]. [dairy ever] = 0 )'
Select {[measures]. [unit sales], [measures]. [dairy ever]} on columns,
[MERs mers who never bought dairy] on rows
From sales
- Period and Period
Function: prevmember, parellelperiod
Calculate the sales value of each product for the same period last year and the annual growth rate.
With member [measures]. [store sales last period] as '([measures]. [store sales], time. prevmember) ', format = '#,###. 00'
Member [measures]. [yearly increase rate] as '([measures]. [store sales]-[measures]. [store sales last period])/[measures]. [store sales last period] ', format_string = 'percent'
Select {[measures]. [store sales], [measures]. [store sales last period]} on columns,
{[Product]. Members} on rows
From sales
Where ([time]. [1, 1998])
In another example, use the parellelperiod function.
With member [measures]. [YTD unit sales] As 'coalesceempty (sum (YTD (), [measures]. [unit sales]), 0) 'member [measures]. [previous YTD unit sales] as '(measures. [YTD unit sales], parallelperiod ([time]. [year])'
Member [measures]. [YTD growth] as '[measures]. [YTD unit sales]-([measures]. [previous YTD unit sales])'
Select {[time]. [1998]} on columns,
{[Measures]. [YTD unit sales], [measures]. [previous YTD unit sales], [measures]. [YTD growth]} on rows
From sales;
- Top NAnalysis
Function: topcount
Solve the number of customers who purchased the first five orders in 1998;
Select {[measures]. [store sales]} on columns,
{Topcount ([MERs]. [customer name]. members, 5, [measures]. [store sales])} on rows
From sales
Where ([time]. [1, 1998])
- Member Filtering
Function: filter, limit t
The solution is to list the names of eligible customers, the number of yearly purchases, and the amount of yearly purchases.
Select {[measures]. [store sales], [measures]. [unit sales]} on columns,
Filter (customers. [name]. members, [measures]. [store sales]> 10000) on rows
From sales
Whare ([time]. [1, 1998])
Another type of member filtering (removing the no media type from all media types) should be set operations.
Select {[measures]. [unit sales]} on columns,
Parameter T ([promotion media]. [media type]. Members, {[promotion media]. [media type]. [no media]}) on rows
From sales
Time period
Function: Sum, ":" Operator
Calculate the sales of stores in the United States within a specified period of time.
With member [time]. [1997]. [six month] As 'sum ([time]. [1]: [time]. [6])'
Member [time]. [1997]. [nine month] As 'sum ([time]. [1]: [time]. [9])'
Select {[time]. [1997]. [six month], [time]. [1997]. [nine month]} on columns,
{[Measures]. [store salse]} on rows
From sales
Where ([store]. [USA])
