MDX查询循环并对列中的特定值求和并仅显示最高总和 [英] MDX query to loop and sum specific values from column and show only the highest sum

问题描述

我是MDX的新手，并尝试执行以下操作，我不知道从哪里开始或如何操作。所以，我有一个Dimension'DaySlice'，它是从一天（00：00-00：14,00：15-00：29等等）开始的15分钟片段和一个名为'number_of_occurrences'的列。
我需要按小时计算number_of_occurrences，意思是：

Hi, I'm new to MDX and trying to do the following and I have no idea where to begin or how to do it. So, I have a Dimension 'DaySlice' that is a 15 minute day slice from a day (00:00 - 00:14, 00:15 - 00:29 and so on) and a column called 'number_of_occurrences'. I need to sum number_of_occurrences by hour, meaning:

00:00 - 00:59（连续4天切片）=有多少number_of_occurrences？

00:15 - 01:14（连续4天切片）=有多少number_of_occurrences？

00:30 - 01:29（连续4天切片）=有多少number_of_occurrences？

00:00 - 00:59 (4 day slices consecutive) = how many number_of_occurrences?
00:15 - 01:14 (4 day slices consecutive) = how many number_of_occurrences?
00:30 - 01:29 (4 day slices consecutive) = how many number_of_occurrences?

ans等等。最后，我需要显示发生最大次数的间隔和最大出现次数的值，例如：

ans so on. At the end I need to show the interval where the max number of occurrences happened and the value of the max number of occurrences, for example:

| 间隔        | 发生次数|

_________________________________

|  interval       |  number of occurrences |
_________________________________

00：15-01：14    |        100.000                  |

_________________________________

00:15-01:14   |       100.000                 |
_________________________________

 很抱歉没有提供我已尝试过的内容，但每个结果都看起来像废话

 Sorry for not providing what I have already tried, but every result looks like nonsense

推荐答案

你好ejfilip，

Hi ejfilip,

你创建了多维数据集吗？尺寸表"DaySlice"是什么？看起来像？度量表怎么样？

Have you created cube? And what does dimension table "DaySlice" look like? How about the measure table?

根据您的描述，您需要使用MDX函数TopCount来实现这一点。以下是供参考的样本。

Based on your description, you need to use MDX function TopCount to achieve that. Here is the sample for your reference.

1。原始数据

select 
[Measures].[Internet Order Count] on 0,
[Date].[Month of Year].[Month of Year] on 1
from [Adventure Works]

输出：

2。成就

SELECT
TopCount([Date].[Month of Year].[Month of Year].Members,1,[Measures].[Internet Order Count]) ON ROWS,
[Measures].[Internet Order Count] ON COLUMNS 
FROM [Adventure Works]

输出

最诚挚的问候，

这篇关于MDX查询循环并对列中的特定值求和并仅显示最高总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！