BOXI3.0: Formula for subtract/deduct one day or more from date field

[Former Member]

Greetings. Its Alias.

I have one question. Actually I already post this question but at designer forum.

I am using SQL.

Example I have Order Date field and from this field I want to subtract the date by minus one day or two days or three days, etc.

I got the solution for designer (example):

DATEADD (dd , -3, EPJ_IPMS.dbo.DO_DATA.DO_DATE_D)

But at webi level it does not work.

I also do not understand why at designer and webi level may have different SQL select statement.

Can someone help me please.

Best regards,

Alias

[Former Member · Accepted solution]

Hi Alias,

Just use this formula

RelativeDate([date_object],day_number)

here day_number be positive or negative.

if your [order date] field is come through a universe as a object then you can easily add/sub date by using that formula:

Hope it will work

Thanks

[Former Member · Accepted solution]

Hi Arif.

It works! Thanks a lot!

Is it true that users in WebI can only create the Varibles which are shown in the formula list?

Best Regards,

Alias

[former_member212749]

Hi Alias,

This is not possible that Designer using the SQL and WebI using Oracle or so.Please check with the Universe check if you are getting data when u create this Object..

Regards

Prashant

[Former Member]

Hi there Prashant.

In my case here is I am using Microsoft SQL Server Enterprise Manager and the data is save inside it.

The Designer and Webi are using same SQL database.

I also created an Object at Designer and the result is good.

But say if I don't want to create the object at Designer and let user create their own variables at webi.

Meaning to educate user to put the variable and formula themselves.

This is where the SQL statement a I mentioned earlier cannot be used at webi.

Best Regards,

Alias

[former_member212749]

Hi Alias,

The Users in WebI can only create the Varibles which are shown in the formula list..I guess there are formulas related to date in the listed hence you can create formulas based on those.

Regards

Prashant