WIS 10037

building reports webintelligence
1

Hi All,

I am trying to find the difference between two date in XIR3.
[Loss Date] [Date Reported to Employer]

For this am doing, =DaysBetween([Loss Date];[Date Reported To Employer])

Getting an error message, The expression or sub-expression at position 13 in the ‘daysbetween’ function uses an invalid data type. (WIS 10037)

can any one help me out on this. Its urgent.

clpa (BOB member since 2011-08-18)

2

Welcome to B:bob:B!

Have you checked both those dates? I bet one of them is stored as a character string. If it is you will need to convert it to a date. As an aside, this is a volunteer board, so it is considered rude to say ‘this is urgent’.

Nick Daniels :uk: (BOB member since 2002-08-15)

3

yes. all of our dates are stored as alpha in format “YYYYMMDDHHMMSS”

So, how do i find the difference between those two. Am new to business objects

clpa (BOB member since 2011-08-18)

4

Try

=DaysBetween(ToDate([Loss Date];"yyyyMMddhhmmss");ToDate([Date Reported To Employer];"yyyyMMddhhmmss"))

sredden1 :us: (BOB member since 2011-06-09)

5

Thank you, its working

clpa (BOB member since 2011-08-18)

6

=DaysBetween(ToDate([Loss Date];“yyyyMMddhhmmss”);ToDate([Date Reported To Employer];“yyyyMMddhhmmss”))

When i use this in the report the data is not comming, the data what it is giving is #ERROR

clpa (BOB member since 2011-08-18)

7

Can you go into universe designer and check the object type (i.e., see whether it’s showing as a date field, or a string, etc.)…? You may need to convert them to dates from there.

sredden1 :us: (BOB member since 2011-06-09)

8

We don’t have an access to the universe. I’ll check it with my universe developer. Thank you

clpa (BOB member since 2011-08-18)

9

if i bring the [Loss Date], [Date Reported to Employer] fields to the report individually am getting the data as 02/15/2000 , 01/13/1999, …

but if i use this as a variable
=DaysBetween(ToDate([Loss Date];“yyyyMMddhhmmss”);ToDate([Date Reported To Employer];“yyyyMMddhhmmss”))

The Data is #ERROR.

clpa (BOB member since 2011-08-18)

10

Okay, one more go at this… :slight_smile: Create a variable for each of the fields using the ToDate function (I’d suggest formatting them as MM/dd/yyyy, since that’s what they seem to be showing as), then put the variables in the DaysBetween formula. It seems to work for me.

So, to be clear - for [Loss Date] create a variable using

ToDate([Loss Date];"MM/dd/yyyy")

Then do the same for [Date Reported to Employer]

ToDate([Date Reported to Employer];"MM/dd/yyyy")

Finally,

DaysBetween([variable1];[variable2])

sredden1 :us: (BOB member since 2011-06-09)

11

I tried in your way. The formula is working but the data is not getting. The data is displaying #ERROR

clpa (BOB member since 2011-08-18)

12

i gave a try like this also, =FormatDate(ToDate([Loss Date];“MM/dd/YYYY”);“dd/MM/YYYY”)

Still getting data as #ERROR

clpa (BOB member since 2011-08-18)

13

Ah, I think I see the problem… the ‘YYYY’ needs to be ‘yyyy’… The date formatting won’t recognize it when it’s capitalized.

sredden1 :us: (BOB member since 2011-06-09)

14

My guess is that you are using a wrong data format in ToDate() function? Can you post the sample dates?

Jansi :india: (BOB member since 2008-05-12)

15

Yes there you go, it is not recognizing “YYYY”. now its getting
Thank you…

clpa (BOB member since 2011-08-18)

16

Total no.of claims selected for a specific selected location where Loss date vs. reported date difference is in the 0-3 days range

for this. how should i write a variable

clpa (BOB member since 2011-08-18)

17

This can be a spearate thread as the problems are totally different. Also while you create a new topic, please also post the code that you’re having in your mind.

Jansi :india: (BOB member since 2008-05-12)