Date Calculation issue in webi

[Anon Anon]

HI

 

My requirement is to calculate the difference between two dates.

Default date format for OOTB column - MM/dd/yyyy hh:mm:ss but for customized column, date format is getting in mm/dd/yy.

For example:

OOTB column - 01/11/2016 09:25:33

zcolumn - 01/20/16

 

Here I have changed the zcolumn o/p in OOTB date format using the formula "FormatDate([Zaccep Time]; "MM/dd/yyyy hh:mm:ss" )". Now the issue is, when I tried to calculate the diff.b/w OOTB column and zcolumn, the data is not getting correctly. PLease help.

For calculating the difference, the formula I am using is PdmSeconds(PdmDownTime('Gold','Mon - Fri { 8:00 am - 5:00 pm }', cr.open_date,cr.zaccep_time))

 

Thanks

Madhavi

[JussiValkonen]

I didn't catch any mention about whether you did the change on the universe side or on the report but most likely you're running into trouble when you try to use the formatted field in calculation. The formatting function returns a string and unless you parse it back to a date object (which would be plain silly) you can't use it in calculation. Instead leave the formatting for the presentation layer, when you've got all the calculations already done. So use the dates as they are for your calculations, they'll be fine for that, and only use the FormatDate() on the report columns/rows.