formatdate # eror inwebi

building reports webintelligence
1

Hi All,

I get #error in webi when I use formatdate or todate in webi xi 3.1. What are the reasons?

I want to change character date (concatenated month+‘/’+date+‘/’+year) function into a date type format using formatdate and todat but i get the above error? It parses ok but when i drag it it gives #error. It also use to work fine in deski

=If [Product Creation Date] >= formatdate(Month(CurrentDate())+“/1/”+ Year([Product Creation Date]); “mm/dd/yyyy”) and [Product Creation Date] <= CurrentDate() then sum([Sale Volume])
What is the workaround.

Can anyone give an actual example, using say the User Response function and converting the character date in mm/dd/yyyy 12:00:00 AM format into a possible date?

ajq

:hb:

anushajq (BOB member since 2005-02-22)

2

i guess dont use format date… instead use to_date function…

As format date accepts only date value as input whereas you are providing character values as input.

To get what you want use

=to_date(Month(CurrentDate())+"/1/"+ Year([Product Creation Date]); "mm/dd/yyyy")

Please let us know your output.

Cheers,

shiva.tomar (BOB member since 2007-10-05)

3

What is the format of this: Year([Product Creation Date])

Does it have separator in the thousands place? ex: 2,009

chris465 (BOB member since 2008-04-29)

4

Try this,

=Sum(If ([Product Creation Date] >=RelativeDate(CurrentDate()-(DayNumberOfMonth(CurrentDate())-1))and [Product Creation Date] <= CurrentDate() then [Sale Volume]))

KhoushikTTT :us: (BOB member since 2005-02-24)

5

[quote:d7500df0a8=“shiva.tomar”]

=to_date(Month(CurrentDate())+"/1/"+ Year([Product Creation Date]); "mm/dd/yyyy")

[/quote]

i hope you want to get the begning of the year right (i.e. 1/1/2009)
then try

=ToDate(FormatDate([your date object];"MM")+"/1/"+FormatDate([your date object];"yyyy"); "mm/dd/yyyy")

It’ll work and give you a date you need.

Cheers,

shiva.tomar (BOB member since 2007-10-05)

6

mtd app vol calculation

=Sum(If [Date Application Created ] <=LastDayOfMonth( [Start Date based on DateApplication Created] ) And [Date Application Created] >= [Start Date based on DateApplication Created] Then [Application Volume])

Start date variable calc:

=ToDate(FormatDate([Date Application Created ];“MM”)+"/1/"+FormatDate([Date Application Created ];“yyyy”); “mm/dd/yyyy”)

but the app volume for first day is not being picked up…

anushajq (BOB member since 2005-02-22)

7

Dear Anusha,

In this formula you are trying to sum a boolean value which is returned by the If condition instead you need to try this

=If([Date Application Created ] <=LastDayOfMonth( [Start Date based on DateApplication Created] ) And [Date Application Created] >= [Start Date based on DateApplication Created]) Then Sum[Application Volume])

I hope this helps.

Cheers,

shiva.tomar (BOB member since 2007-10-05)

8

Correct, regarding summing the boolean…:).

Personally I have always used NewyorkBaashas suggestion for this sort of thing, much tidier IMO:-

=Sum(If ([Product Creation Date] >=RelativeDate(CurrentDate()-(DayNumberOfMonth(CurrentDate())-1))and [Product Creation Date] <= CurrentDate() then [Sale Volume]))

You are missing some brackets.

Mak 1 :uk: (BOB member since 2005-01-06)