New column for date based on exisiting columns

[Former Member]

Hi,

I have a Query output where i am having two dates, "Expected Date" and "Revised Dates".

Now, i want to have a column which would say " Exp/Rev Date" and should have the value of Revised Date if it not ZERO. IF revised date is zero, that mean 00/00/.0000 or not available, it should show the value of "Expected Date".

It would also want to hide the existing columns of Expected and Revised dates and instead show only the new column which is Expe/Rev date.

Any idea how we can do it at query level in the simplest way ?

Regrds,

Tapish

[Former Member · Accepted solution]

Create two formula variables of replacement path type....one would read the exepcted data dn other would read revised dae.

Then create in formula in the key figure structure which will compare both the dates and return one date as result according to your condition...you can put both dates in rows and hide them ...so that finally you will have only date.

Rgds

Amit

[Former Member]

Thanks

[Former Member]

instead of comparing it with zero try using another function which returns whether the value is blank or not i think "Count" is the one...letme know two scenarios one with true condtion and one with false with date values and the result you are getting..

rgds

Amit

[Former Member]

i dont think even that will work as the dates are mentioned as 00/00/0000 which is niether Zero as zero is a numeric nor blank.

how about changing these dates to a numeric -> using the formula to choose among these numerics -> retrieving the required date -> changing it back to date and display.

Here what i would like to know is, how can i change the date to a numeric and back ?

Tapish

[Former Member]

At query level you can create two formula variables and replace them with the mentioned infoObjects. Use a formula to add the logic you described.

Kind regards,

Alex

[Former Member]

I have my dates in the column under key figures !

[Former Member]

Hello,

My dates are in the column under Kety figures.

Can you give a step by step procedure how to do it,haven't done it before.

Regards,

Tapish

[Former Member]

We need a characterstic reference for a formula variable.

These dates are defined as restricted key figures.

Ok, let me state this more clearly.

Actually, these dates are derrived from a combination of a characterstic" milestones" (say with values X,Y and Z) and dates (say actual date , review date and expected date).

Hence the result is a set of restricted key figures in report as columns like:

actual date for X I actual date for Y I actual date for Z I review date for X I review date for Y I review date for Z I expected date for X I expected date for Y I expected date for Z I

Now i need to replace all the set of Expected date and Reviewed date columns by only one column each for X , Y and Z called :Exp/Rev date forX/Y/Z.

The Report should look like:

actual date for X I actual date for Y I actual date for Z I Exp/review date for X I Exp/review date for Y I Exp/review date for Z

This new column will be filled with the logic as :Fill in Reviwed date if it is not zero, else fill Expected date.

Hope this clarifies.

Kind Regards,.

Tapish

[Former Member]

New Formula = ((Expected date > 0) * Expected date) + ( Expected date <= 0) * revised date))

Rgds

amit

[Former Member]

Thanks Amit,

However as expected, it did not work. I am not sure how would a boolean formula work on date ?

The result was , the row where i was expecting some result as there is a reviewd date , it gave some numeric value (732,617) which is weird and the rows where there are not reviewed values it gave blanks.

Regards,

Tapish