cancel
Showing results for 
Search instead for 
Did you mean: 

Calculating the Days between two dates

former_member200330
Participant
0 Kudos

Hi All,

I need to compare the Bill Date of the material to the date that is entered by the user using which i captured using UserResponse(). For the user to enter the date, i am using the Calendar Day Dimension in filters pane. Now wen i am trying to calculate the Days between the two dates using the DatesBetween() it is saying that there is no data to retrieve.

I guess this is because i am trying to use the Calendar Day dimension to filter the data.

Please Help,

Thanks & Regards,

NRR

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

I would have used something like below-with use reponse and another date object

=DaysBetween(ToDate( UserResponse("sPDT");"MM/dd/yyyy hh:mm:ss a");LastExecutionDate())

Hoep this helps

Regards,

Bilahari M

former_member200330
Participant
0 Kudos

Hi Bilhari,

I have to compare the User input with Bill date which is present in an object from the Infocube.

=DaysBetween(ToDate( UserResponse("sPDT");"MM/dd/yyyy hh:mm:ss a");LastExecutionDate())

Can you please tell me why in the above formula, after the UserResponse() "MM/dd/yyyy" is used? and what is the purpose of the LastExecution Time???

Please Help,

Thanks & Regards,

Raja.N

Former Member
0 Kudos

Can you please tell me why in the above formula, after the UserResponse() "MM/dd/yyyy" is used? and what is the purpose of the LastExecution Time???

1.Usually Calendar prompt format looks something like this "11/2/2008 12:00:00 AM" -->Extra fileds are for Date format mask upto seconds.You can put date format based on how you get data from calendar

2.LastExecution Time is just an example variable.Yo should replace that with your dimension object/variable.

Regards,

Bilahari

Former Member
0 Kudos

Hi,

Can u give the format in which the Calendar Day dimension and Calendar Day Key appears.......

Regards,

ArunKG

former_member200330
Participant
0 Kudos

Hi Arun,

I am directly using the Calendar day Dimension so that i can give the user with a calendar to select the dates, and the bill date for the materials comes from BW cube.

The format for Bill Date is(dd/MM/yyyy) [01/10/2011]

The format for comparison Date is (MM/dd/yyyy) [9/16/2011]

Please Help,

Thanks & Regards,

NRR

Former Member
0 Kudos

Hi NRR,

Plz give me the Calendar Day KEY format not the date dimension format, then I can give you a solution.

Regards,

ArunKG

former_member200330
Participant
0 Kudos

Hi Arun,

On consultation with my BI guys i came to know that the format is dd/mm/yyyy in the Info cube for the Calendar Day Key

Thanks & Regards,

Raja.N

Former Member
0 Kudos

Hi,

I think you didn't understand my question.

You have created the Webi report right?

In the edit Query of your Webi report, in your left side you can see the cube dimensions and Key figures OK.

From there only you have selected your Calendar Date OK.

In that you are selecting only L01 Calendar Date OK.

When you expand the L01 Calendar Date you will get Calendar Date Key which will be in String format OK.

Plz check the same, for this you don't need to consult with your BI Consultant, you itself can check it.

For BI guys the date will only be in Date format OK.

Regards,

ArunKG

former_member200330
Participant
0 Kudos

Hi Arun,

I have checked in the Universe and it is in character format. I changed it to Date format. Is that wat i am supposed to do?? and by the format is "MM/dd/yyyy"

Thanks & Regards,

NRR

Former Member
0 Kudos

Hi NRR,

Plz don't change it, let it be like that.

You don't need to check in the Universe. Its OK that you understood that its in Character format.

You Plz check in your Webi report.

There you select Calendar Date Key also in the Result Objects in Edit Query of Webi Report and check what difference in format Calendar Date Dimension and Calendar Date Key appears.

Check the same and tell me.

Why I am asking you because I am getting Calendar Date Dimension in MM/dd/yy (Date format) format and Calendar Date Key in dd.MM.yyyy (Character format)

Regards,

ArunKG

former_member200330
Participant
0 Kudos

Hi Arun,

I tried the same and felt dumb to find that there are no values being returned into the report for "Calendar Date Dimension" and # values for "Calendar Date Key". but from the calendar drop down the formats are:

Calendar Date Dimension :MM/dd/yyyy

Calendar Date Key : couldnt find as only # is being displayed.

Please Help,

Thanks & Regards,

NRR

Former Member
0 Kudos

Hi NRR,

If you want to convert the User date from MM/dd/yyyy to dd/MM/yyyy.........

Then Try this function..........

FormatDate(ToDate(Left(UserResponse("Enter Calendar day:");Pos(UserResponse("Enter Calendar day:");" ")-1);"M/d/yyyy");"dd/M/yyyy")

Regards,

ArunKG