how to get days between 2 dates using dimensional data.

[jd]

how to get days between 2 dates using dimensional data.

[venkiatmaruthi]

If you have two columns as start_date and End_date
then
if you write a query, you can use DataItem and directly right [End_date - Start_date]

this directly returns the difference interms of Days like 90 days, 150 days, 20 days...etc.


OR you can use this function _days_between(end_date,start_date). This returns the days in numbers like 90, 150, 20...etc.

Hope this helps.

Thanks.

[jd]

Hi venkiatmaruthi,

Thanks for the reply, as you mentioned below [End_date - Start_date]
or
_days_between(end_date,start_date) both are not working.

with [End_date - Start_date] I am getting following error:

OP-ERR-0062
       The context for the arithmetic operator '-' is invalid. A numeric value is required.

_days_between(end_date,start_date) getting following error:

UDA-EE-0094 The operation "_days_between" is invalid for the following combination of data types: "varchar" and "varchar"RSV-SRV-0042 Trace back:RSReportService.cpp(771): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(265): 

any idea how to resolve..

[MFGF]

Your problem here is that you are dealing with members not date-time values.  What you see displayed for each date member is its caption, but in reality the members are being addressed via their MUNs (Member Unique Names).

You can get hold of the captions by using the caption() function, but then the challenge is to convert the text-based captions into real dates that you can use.  One option is to extract the year, month and day parts from the captions using substring() functions, then convert these values to integers using cast(), and finally put them together as dates using _make_timestamp().  You should then be able to use these in your _days_between() expression.

Good luck!

MF.

[CognosPaul]

No substrings in cubes! Bad MF!

Since this is a dimensional package you'd have to use a dimensional solution.

The first question is what type of dimensional model is this? DMR, PowerCubes, and SSAS each having solutions. If it's DMR or SSAS you can set a date-time attribute to the members, then you could do something like:

filter([Cube].[TimeDim].[TimeHier].[Date],[Cube].[TimeDim].[TimeHier].[DateAttribute] >= #prompt('StartDate','date')# and [Cube].[TimeDim].[TimeHier].[DateAttribute] <= #prompt('EndDate','date')#)

The second question is what version of Cognos do you have. The solution I have for powercubes is predicated on being able to use the macro function timeStampMask (which is not available in 8.2).


Wow, I am tyrant when it comes to making dimensional reports. Sorry MF! ;D

[MFGF]

Thanks Mr Tyrant! ;)

To be fair, substring() functions are OK as report expressions - the key aim is to make sure they do not impact the MDX.  Notwithstanding, I recognise an MDX Jedi Master when I see one, Mr M, and respectfully thank you for giving me the metaphorical slapping I deserve! :D

MF.

[jd]

Hi MFGF,

as you mentioned below I used caption() and substring() functions. using substring I got split the date part like
year=2009, month=09, day =12 etc..

now I have 3 separate data items for year month day. please tell me that how to use cast function for those data items.

I am kind of new to cast and time stamp functions..

Thanks,

[MFGF]

Try something like
Cast([Your extracted year],integer)

If this works, do the same for the month and day values.

MF.

[CognosPaul]

(Apologies first, I do not have access to the standard Cognos samples. All of the examples I give will be based on my clients cubes.)

I mentioned before that I have another way to do this, here it is.

The first trick is to find the member of the start and end dates. If you're using tree or value prompts this won't pose a problem. However if you're trying to use date prompts you'll have to use a little trickery to get it. If you're not using a date prompt skip the following.

First you need to look at one of the date members in the cube. Create a new data item in your query, and drag a date member into it. The member should look something like this:
[Operations_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].[20080107]
That is the January 7, 2008 member from the Day level of the Date Hierarchy in the Date Dimension of the Operations Cube.

Since the date is easy to see, we can use a prompt macro with the timestamp macro function to get to it:

Code Select Expand

[IL_Operations_Finance_Day_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#

Before I continue, I'll explain the macro:

Code Select Expand

#sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#
Sb means square bracket, this will wrap whatever is inside the parens with [ and ].

Timestampmask will convert any timestamp from the format 'yyyy-mm-dd hh:mm:ss[.ff]+hh:mm' to whatever format you want. In this case yyyymmdd.

The prompt will return a date in the yyyy-mm-dd. If no date is selected, it will return the current timestamp (thus making this prompt optional). The 'T00:00:00Z' at the end will append the time portion of a timestamp after any date the user selects.

Now that we have a way of identifying the start and end date members we have to find a way to get all of the members between them.

To do that we'll use the periodsToDate, Except and Union functions.
Start with periodsToDate.

Code Select Expand

periodsToDate ([Operations_Cube].[Date].[Date].[Date],[Operations_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('EndDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)
This will prompt the user for an end date, and generate a list of dates from the first date in the cube to the date the user selected.

The except function will filter out all members from the first set (the EndDate set) that share members with the second set:

Code Select Expand

except(
periodsToDate ([Operations_Cube].[Date].[Date].[Date],[Operations_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('EndDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)
,periodsToDate ([Operations_Cube].[Date].[Date].[Date],[Operations_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#))

As you can see, the periodsToDate function is being used to create a set of members from the beginning to the member selected as the start date.

The except function will remove all of the members from the EndDate set that exist in the StartDate set. Including the selected start date! To force the start date back into the final set we can use the union function:

Code Select Expand

union([Operations_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#,
except(
periodsToDate ([Operations_Cube].[Date].[Date].[Date],[Operations_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('EndDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)
,periodsToDate ([Operations_Cube].[Date].[Date].[Date],[Operations_Cube].[Date].[Date].[Day]->:[PC].[@MEMBER].#sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)))

This is the final function. It will return a set of dates from the StartDate to the EndDate. It's important to remember that you absolutely must have these dates in your cube. If the user selects any dates outside of the range in your cube the report will return an error.

You can now put this data item as a node in a crosstab or graph, or even put it into the slicer of the query.

Open the below XML in RS, change the package to your cube and make the necessary changes. Remember the timeStampMask may have to be changed to fit your cubes.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-nz">
<modelPath>/content/package[@name='IL_Operations_OTI']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><table><style><defaultStyles><defaultStyle refStyle="tb"/></defaultStyles><CSS value="border-collapse:collapse"/></style><tableRows><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>Start Date</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectDate parameter="StartDate" selectDateUI="editBox" name="StartDatePrompt"/></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem><dataSource><staticValue>End Date</staticValue></dataSource></textItem></contents></tableCell><tableCell><contents><selectDate selectDateUI="editBox" parameter="EndDate" name="EndDatePrompt"/></contents></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell colSpan="2"><contents/></tableCell></tableCells></tableRow></tableRows></table><crosstab horizontalPagination="true" name="DateExampleCrosstab" refQuery="Query1">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Actual" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Dates" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Actual"><expression>[Operations_Cube].[Measures].[Actual]</expression></dataItem><dataItem name="Dates"><expression>union([Operations_Cube].[Date].[Date].[Day]-&gt;:[PC].[@MEMBER].#sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#,
except(
periodsToDate ([Operations_Cube].[Date].[Date].[Date],[Operations_Cube].[Date].[Date].[Day]-&gt;:[PC].[@MEMBER].#sb(timestampmask(prompt('EndDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)
,periodsToDate ([Operations_Cube].[Date].[Date].[Date],[Operations_Cube].[Date].[Date].[Day]-&gt;:[PC].[@MEMBER].#sb(timestampmask(prompt('StartDate','date',$current_timestamp,'','','T00:00:00Z'),'yyyymmdd'))#)))</expression></dataItem></selection></query></queries></report>

[MFGF]

I'm constantly amazed (and humbled) by the knowledge and skill of many of the posters on this forum.  To those of you who regularly answer queries and help others with issues (you know who you are!) a huge thanks!  Oh, and thanks to you too Paul! ;)