Counting consecutive week.

[lalitha.nov20]

Hi,

I am working on requirement where I need to display the consecutive weeks the trend as remained same as of current week for which the report is executed.

For example

06/30/2012 : Green
07/07/2012  : Red ( measure between 95 and 105)
07/14/2012 : Red ( measure between 95 and 105)
07/21/2012 : Green (measure > 105)
07/28/2012 : Green.   (measure > 105)



The report should display consecutive Week count as 2 as the lastest week for which for report executed(07/28/2012) is in Green and also the prior week. Week 07/14/2012 will not be counted as it is in red.

The data source is SSAS cube.

thanks in advance.
Lalitha

[CognosPaul]

If I understand, the user selects a set of weeks, and the total row should have a 2, indicating that there were a maximum of 2 consecutive weeks in the selected period in which a criteria was met?

So if the user selects 10 weeks, with first 4 weeks and last 5 weeks green, the value should return 5?

[lalitha.nov20]

Hi Paul,

My requirement is to count the number of prior weeks which was in the same as the current week.

Example, I have select 07/21/2012 as my current week and I am looking at the last 13 weeks, I would check, previous week of 07/21/2012, if it has the same trend as 07/21/2012, if yes the I would continue checking for the previous weeks for the same trend as current week. I should the loop when the first previous week does not match

06/30/2012 : Green
07/07/2012  : Red ( measure between 95 and 105)
07/14/2012 : Green ( measure between 95 and 105)
07/21/2012 : Green (measure > 105)

In the above example, the current week 07/21/2012 and previous week 07/14/2012 is green and matches the trend of current week. Total count of consecutive week 2. The count breaks when the  week 07/07/2012 is red. This does not match the current week trend. 06/30/2012 week is not counted as it in not consecutive week for current week.

Hope this requirement is clear

[CognosPaul]

Unfortunately OLAP doesn't have a nice way of counting consecutive months, so we need to cheat a bit.

I used months in my example, but it works the same way. First, I counted the number of months I have. Then I found the last member that didn't meet the goal, counted the number of periods previous to it, and subtracted one number from the other.

XML is on the sales and marketing cube.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Month" aggregate="none" rollupAggregate="none"><expression>filter([sales_and_marketing].[Time].[Time].[Month],[Revenue]&gt;0)</expression></dataItem><dataItem name="Revenue"><expression>[sales_and_marketing].[Measures].[Revenue]</expression></dataItem><dataItem name="count"><expression>count([One] within set [Month])</expression></dataItem><dataItem name="Last Month that does not meet goal"><expression>item(tail(filter([Month],[Revenue]&lt;[goal])),0)</expression></dataItem><dataItem name="Months"><expression>total([One] within set
periodsToDate([sales_and_marketing].[Time].[Time].[Time],
[Last Month that does not meet goal]))</expression></dataItem><dataItem name="Last Consecutive Count"><expression>[count]-[Months]</expression></dataItem><dataItem name="One"><expression>member(1,'a','a',[sales_and_marketing].[Measures])</expression></dataItem><dataItem name="goal"><expression>160000000</expression></dataItem></selection>
</query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>


<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="count" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="count"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Months" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Months"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Last Consecutive Count" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Last Consecutive Count"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</contents>
</pageBody>


</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes></report>

[lalitha.nov20]

Hi Pual,

Thanks for the reply. The code didn't opne in report studio. Though I tried to understand the logic used. I cannot to periodtodate() as I m filter the date to last 14 and the trend is to calculated with this 14 week. Not user how to count from the week where the trend changed to current week.

Thank you,
lalitha

[CognosPaul]

Which version of Cognos are you using?

In the xml, Try changing "http://developer.cognos.com/schemas/report/11.0" to "http://developer.cognos.com/schemas/report/9.0"

Depending on how you're filtering the query, the periodsToDate function should still work.

[lalitha.nov20]

I am using Cognos 10.1