Issue working with _add_days function in filter

[D1234]

Hello,

I am currently working on a report which needs to automatically update data based on a filter to reflect the last four weeks of data.

My filter currently is:

[Date] between add_days(current_date,(-28)-[Day of Week Current Date]) and _add_days(current_date,-[Day of Week Current Date]))

- where [Day of Week Current date] is a field which gives me the numerical day for the day of the week of current date.

The issue is that I need this filter to automatically pull data from the last four weeks using the previous Sunday as the starting day of the week. The formula currently works from the current date resulting an inconsistent number of weeks of information being displayed. I am looking for a way to change this formula to only show me four weeks back from the most recent Sunday.

Thanks
 

[New_Guy]

Hi,
Try creating a data item _week_of_year([Yourdatefiled]) and use this in the filter [_week_of_year([Yourdatefiled])] between _week_of_year (current_date)-1 and _week_of_year (current_date) - 5 and this should work on any weekday. If you want to run it on any day then we have to add logic with a case statement using _day_of_week.
Good luck
New guy

[D1234]

Thanks for the reply!

I will try this and let you know how this works.

[D1234]

I tried this method and it seems it does not work. I ended up creating a field for _week_of_year(current_date) and created a filter which is:

[Date] between _week_of_year(current_date)-1 and _week_of_year(current_date)-5

When this runs it displays no information for some reason. I additionally tried to make each of the _week_of_year(current_date)-X its own field; this did not have any effect. Is there perhaps something with the syntax that is the issue?

Thanks

[New_Guy]

Hi,
Create a list with your [date], _week_of_year ([date]) as columns with no filters and let us know if you are seeing any data and post a sample screenshot if you can. And also check the [Date] is Date type.
Good luck
New guy

[D1234]

It seems that this new filter I just made : [Date] between [Week of the year -1] and [Week of the Year -5] is the cause of the data disappearing. When the fields are dropped into a list under the old filter, everything populates and all fields work as needed. For some reason, this new filter is causing all the data to be filtered out. I tried also only using this filter by itself and the same thing happened, which confirms this theory. Unfortunately I am unable to provide screenshots as I am working with confidential data. There may perhaps be something in my data that is causing this but I am unsure as of now. Are there perhaps any other methods which could use to achieve what I need?

Thanks for the help

[New_Guy]

Hi,
Reverse this [Date] between [Week of the year -1] and [Week of the Year -5] to [Date] between [Week of the year -5] and [Week of the Year -1]. For time based filtering the lowest value should be in the first argument when you use  between. If this doesnt work, we will think of an alternative.
Good luck
New guy 

[D1234]

Tried this and same issue happened. Not sure what the problem is. Also tried making [Date] between [Week of the year -5] and [Week of the Year -1] its own field and the report would not run with this field.

Thanks

[New_Guy]

Hi,
Below xml is a sample that is built with audit package and it works fine.
Good luck
New guy

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Audit']/folder[@name=' Audit']/package[@name='Audit']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Report name" aggregate="none" rollupAggregate="none"><expression>[Audit].[Run Reports].[Report name]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Report search path" aggregate="none" rollupAggregate="none"><expression>[Audit].[Run Reports].[Report search path]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Time stamp" aggregate="none" rollupAggregate="none"><expression>[Audit].[Run Reports].[Time stamp]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="4" output="no"/><XMLAttribute name="RS_dataUsage" value="identifier" output="no"/></XMLAttributes></dataItem><dataItem name="week of timestamp"><expression>_week_of_year ([Time stamp])</expression></dataItem><dataItem name="week of timestamp -1"><expression>(_week_of_year (current_date))-1</expression></dataItem><dataItem name="week of timstamp -3"><expression>(_week_of_year (current_date))-3</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[week of timestamp] between [week of timstamp -3] and [week of timestamp -1]</filterExpression></detailFilter></detailFilters></query>
</queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>
<list refQuery="Query1" horizontalPagination="true" name="List1">



<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="ls"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Time stamp"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Time stamp"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="week of timestamp"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="week of timestamp"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="week of timestamp -1"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="week of timestamp -1"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="week of timstamp -3"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="week of timstamp -3"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><sortList><sortItem refDataItem="Time stamp" sortOrder="descending"/></sortList></list>
</contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
<promptPages><page name="Prompt Page1">
<pageHeader>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue/>
</dataSource>
<style>
<defaultStyles>
<defaultStyle refStyle="tt"/>
</defaultStyles>
</style>
</textItem>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ta"/>
</defaultStyles>
</style>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="hp"/>
</defaultStyles>
</style>
</pageHeader>
<pageBody>
<contents><block><contents><textItem><dataSource><staticValue>Time stamp</staticValue></dataSource></textItem></contents></block><block><contents><selectDateTime parameter="Time stamp" required="true" multiSelect="false" range="true"><XMLAttributes><XMLAttribute name="dataType" value="xsdDateTime"/></XMLAttributes></selectDateTime></contents><style><CSS value="margin-bottom:12px"/></style></block></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="py"/>
</defaultStyles>
</style>
</pageBody>
<pageFooter>
<contents>
<promptButton type="cancel">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="back">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="next">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
<promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="fp"/>
</defaultStyles>
</style>
</pageFooter>
<style>
<defaultStyles>
<defaultStyle refStyle="pp"/>
</defaultStyles>
</style>
</page></promptPages></layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2013-05-23T19:10:00.035Z" output="no"/></XMLAttributes></report>

[D1234]

I will try using this and see how it works with my data.

Thank you for all the help

[MFGF]

I tried this method and it seems it does not work. I ended up creating a field for _week_of_year(current_date) and created a filter which is:

[Date] between _week_of_year(current_date)-1 and _week_of_year(current_date)-5

When this runs it displays no information for some reason. I additionally tried to make each of the _week_of_year(current_date)-X its own field; this did not have any effect. Is there perhaps something with the syntax that is the issue?

Thanks

Hi,

That expression doesn't seem to make any sense. You are comparing a date (2017-12-05 eg) with the result of a _week_of_year() function (48 eg) so you're never going to get a result. Shouldn't you be comparing the _week_of_year() of your date with the _week_of_year() of the current date?

MF.

[D1234]

Got it working

Thanks!

[venkat01]

Try this datefield>=trunc(next_day(_add_days(current_date,-29),'monday'),'day') and datefield<=current_date this gives 4weeks back starting monday to current date data you can replace monday with anyday and if you dont want till current date meant to say data to be stopped at current week monday you can replace current_date in the second expression with same first expression by replacing number 29 with 8 maybe helpful

Sent from my GT-N8000 using Tapatalk