Cognos计算方差交叉表（维） [英] Cognos Calculate Variance Crosstab (Dimensional)
问题描述
这与 Cognos计算方差交叉表（关系）非常相似，但是我数据源是维的。
This is very similar to Cognos Calculate Variance Crosstab (Relational), but my data source is dimensional.
我有一个简单的交叉表，例如：
I have a simple crosstab such as this:
| 04-13-2013 | 04-13-2014
---------------------------------------
Sold | 75 | 50
Purchased | 10 | 15
Repaired | 33 | 44
- 过滤器：用户选择1个日期，然后我们将该日期包括在内加一年前。
- 维度：日期是YQMD层次结构中的日期级别。
- 度量：我们从度量中显示各种度量尺寸。
- 已出售
- 已购买
- 已修复
- Filter: The user selects 1 date and then we include that date plus 1 year ago.
- Dimension: The date is the day level in a YQMD Hierarchy.
- Measures: We are showing various measures from a Measure Dimension.
- Sold
- Purchased
- Repaired
这是报表工作室的样子：
Here is what is looks like in report studio:
| <#Day#> | <#Day#> --------------------------------------- <#Sold#> | <#1234#> | <#1234#> <#Purchased#> | <#1234#> | <#1234#> <#Repaired#> | <#1234#> | <#1234#>我希望能够像每种度量一样计算两个时间段之间的百分比差异
I want to be able to calculate the variance as a percentage between the two time periods for each measure like this.
| 04-13-2013 | 04-13-2014 | Var. % ----------------------------------------------- Sold | 75 | 50 | -33% Purchased | 10 | 15 | 50% Repaired | 33 | 44 | 33%我在<＃Day＃>右侧添加了一个查询表达式，如下所示
I added a Query Expression to the right of the <#Day#> as shown below, but I cannot get the variance calculation to work.
| <#Day#> | <#Variance#> --------------------------------------- <#Sold#> | <#1234#> | <#1234#> <#Purchased#> | <#1234#> | <#1234#> <#Repaired#> | <#1234#> | <#1234#>这些是我尝试过的表达式以及得到的结果：
These are the expressions I've tried and the results that I get:
一个经过硬编码的表达式有效，但仅适用于该1个度量：
An expression that is hard coded works, but only for that 1 measure:
total(case when [date] = 2014-04-13 then [Sold] end) / total(case when [date] = 2013-04-13 then [Sold] end) -1我认为 CurrentMember 和 PrevMember 工作，但会产生空白单元格：
I thought CurrentMember and PrevMember might work, but it produces blank cells:
CurrentMember( [YQMD Hierarchy] ) / prevMember(CurrentMember([YQMD Hierarchy])) -1我认为这是因为 prevMember 产生空白。
I think it is because prevMember produces blank.
prevMember(CurrentMember([YQMD Hierarchy]))仅使用 CurrentMember 会同时给出两列：
Using only CurrentMember gives a total of both columns:
CurrentMember([YQMD Hierarchy])什么表达n我可以用来利用我的尺寸模型并添加具有％方差的列吗？
What expression can I use to take advantage of my dimensional model and add a column with % variance?
这些是我用于研究的页面：
These are the pages I used for research:
- Variance reporting in Report Studio on Cognos 8.4?
- Calculations that span dimensions - PDF
- IBM Cognos 10 Report Studio: Creating Consumer-Friendly Reports
推荐答案
我希望有更好的方法可以做到这一点。我终于找到了描述此问题的一种方法的资源。使用 tail 和 head 函数，我们可以到达第一个周期和最后一个周期，从而计算％方差。
I hope there is a better way to do this. I finally found a resource that describes one approach to this problem. Using the tail and head functions, we can get to the first and last periods, and thereby calculate the % variance.
item(tail(members([Day])),0) / item(head(members([Day])),0) -1这个想法来自 IBM Cognos BI –使用维度函数确定当前期间。
示例2 –通过过滤度量数据查找当前时间段
Example 2 – Find Current Period by Filtering on Measure Data
如果OLAP或DMR数据源已填充未来的时间段（例如年末或未来的年份），则当前时间段的计算更加复杂。但是，仍然可以通过找到包含给定度量的数据的最新期间来确定。
If the OLAP or DMR data source has been populated with time periods into the future (e.g. end of year or future years), then the calculation of current period is more complicated. However, it can still be determined by finding the latest period that has data for a given measure.
item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]), tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time])) is not null), 1), 0)这篇关于Cognos计算方差交叉表（维）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
