MDX查询以计算产品名称的数量 [英] MDX query to count number of product names

问题描述

我是MDX的新手，正在尝试每周计算产品名称的数量.我已经编写了下面的MDX查询，但结果得到了所有产品计数.

I am new to MDX and trying to count number of product names on a week basis. I have written the below MDX query, but I get all product count as a result.

WITH MEMBER [Measures].[Count Of Members] AS
Count(Existing [Product Dimension].[ProductName].Members)

SELECT {[Measures].[Count Of Members]} ON 0,  
NON EMPTY
{
[Date Dimension].[Financial WC]
} ON 1

FROM [Cube]
WHERE ([Date Dimension].[FinancialYear].&[2018/19],
       [Sales].[PaymentType].&[Delivery])

我得到的结果是:

全部230"

因为共有230个产品名称

as there are total 230 product names

但是我想要的结果是:

第1周50 第2周73 第3周34 . .

week1 50 week2 73 week3 34 . .

推荐答案

欢迎使用MDX.您的任务有问题.当您说要计算每周的产品名称"时，您将需要根据事实对它们进行计数.例如，该周售出的产品数量.计数的方法基本上是对产品维度中的所有产品进行计数.

Welcome to MDX. Your quest has an issue. When you say you want to count "product names on a week basis" you will need to count them on the basis on a Fact. So for example the count of the products that were sold in that week. The way you are counting is basicly going to count all the products in your product dimension.

对于以下示例，我正在使用冒险作品.

For the below sample i am using adventure works.

在下面的示例中，我想知道第1周售出了多少种产品.在这里，我们谈论的是产品类型的数量而不是单位.下面的查询可以做到这一点.

In the below sample i want to know how many type of products were sold in week 1. Here we are talking about count of product type not units. The query below does that.

select [Measures].[Internet Sales Amount] on columns,
non empty
([Date].[Calendar Week of Year].&[1],[Product].[Product].[Product])
on rows 
from [Adventure Works]

结果:

结果计数

Notics在结果计数中显示97行.这意味着售出了96种产品，结果还包括列名称，因此计数为97.

Notics in the result count it shows 97 rows. this means 96 products were sold,the result also includes the column name hence the count is 97.

现在，让我们看一下解决您的问题的查询.

Now lets take a look at the query that solves your issue.

with member
[Measures].[Test]
 as 
count(
filter
(([Date].[Calendar Week of Year].currentmember, [Product].[Product].[Product]),[Measures].[Internet Sales Amount]>0)
)
select 
[Measures].[Test]
on columns,
[Date].[Calendar Week of Year].[Calendar Week of Year]
on rows 
from [Adventure Works]

结果

因此，这表明已出售了96种产品.

So this shows that 96 products were sold.

查询背后的逻辑是，我们计算一周内销售额大于0的所有产品. ".currentmember"转换为当前星期，因此我们可以计算任何一周.

The logic behind the query is we count all the products that has >0 sales for a week. ".currentmember" transalates to the current week, hence we can calulate for any week.

这篇关于MDX查询以计算产品名称的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！