How to divide two dates in rational insight report
Actually i want to use schedule variance. My schedule variance formula is ((([Actual Finish Date]-[Actual Start Date])-([Planned_End_Date]-[Planned_Start_Date]))/([Planned_End_Date]-[Planned_Start_Date]))*100.
I can subtract it but cannot divide it.
But it is showing error like
|
|
|
|
|
4 answers
Hi,
Have you tried to use DaysBetween Cognos formula ?
Another thing to try is divide the formula in multiplus data items instead of do the whole formula in just one data item and run the report as tabular data each time that you create a new data iten to ensure that the data item created is working fine.
I third think to try is use CAST formula to cast your result to integer eg: cast(data_item1,integer).
Good Lucky
Have you tried to use DaysBetween Cognos formula ?
Another thing to try is divide the formula in multiplus data items instead of do the whole formula in just one data item and run the report as tabular data each time that you create a new data iten to ensure that the data item created is working fine.
I third think to try is use CAST formula to cast your result to integer eg: cast(data_item1,integer).
Good Lucky
I recommend to you to separate each piece of your formula into data items to "debug" where the issue is happening. eg:
Your Data Item
(([Actual Finish Date]-[Actual Start Date])-([Planned_End_Date]-[Planned_Start_Date]))/([Planned_End_Date]-[Planned_Start_Date]))
Create:
Data Item 1
[Actual Finish Date]-[Actual Start Date]
Data Item 2
[Planned_End_Date]-[Planned_Start_Date]
Data Item 3
[Planned_End_Date]-[Planned_Start_Date]
Data Item 4
Data Item 1 - Data Item 2
Data Item 5
Data Item 4 / Data Item 3
Data Item 6
Data Item 5 * 100
Run as tabular data after create each data item and if necessary put CAST formula into Data Item 1, 2 and 3. eg: cast(([Actual Finish Date]-[Actual Start
Date]),integer)
After found the issue and correct, you can put the formula again into just one data item.
This approach
help
a lot to find and solve problems in Cognos.
I hope this help you!
Your Data Item
(([Actual Finish Date]-[Actual Start Date])-([Planned_End_Date]-[Planned_Start_Date]))/([Planned_End_Date]-[Planned_Start_Date]))
Create:
Data Item 1
[Actual Finish Date]-[Actual Start Date]
Data Item 2
[Planned_End_Date]-[Planned_Start_Date]
Data Item 3
[Planned_End_Date]-[Planned_Start_Date]
Data Item 4
Data Item 1 - Data Item 2
Data Item 5
Data Item 4 / Data Item 3
Data Item 6
Data Item 5 * 100
Run as tabular data after create each data item and if necessary put CAST formula into Data Item 1, 2 and 3. eg: cast(([Actual Finish Date]-[Actual Start
After found the issue and correct, you can put the formula again into just one data item.
This approach
I hope this help you!
Comments
I have also found this to be helpful in solving problems. An additional thing I have found helpful is to add a filter to the query so that only one record is returned. In some cases there is a record with unexpected values that is the cause of the problem, so getting the query to work on a single record first makes the problem easier to solve.
1 vote
Hi,
There are OOTB reports for schedule variance at
Public Folders > Sample Report Definitions > Insight > Data Warehouse Reports,
named "Iteration Variance Details" and "Project Variance Details". In this report, the formula is simpler, such as
Actual schedule variance = (Actual finish date - Planned finish date) * 100 / Planned duration
Perhaps you can review these OOTB report templates, and customize based on them.
There are OOTB reports for schedule variance at
Public Folders > Sample Report Definitions > Insight > Data Warehouse Reports,
named "Iteration Variance Details" and "Project Variance Details". In this report, the formula is simpler, such as
Actual schedule variance = (Actual finish date - Planned finish date) * 100 / Planned duration
Perhaps you can review these OOTB report templates, and customize based on them.
Comments
But for this report, when i run, it is not displaying any data. Like for using that formula they pulled actual duration,planned duration metrics, I want the path of those metrics
(Actual finish date - Planned finish date) * 100 upto this it is working after that putting / causes problem and from where i can get planned duration
As discussed, I think you need to create a sub query like (Actual finish date - Planned finish date) * 100 / Query2. Query2 = ([Planned_End_Date]-[Planned_Start_Date]). See the OOTB report "Project Variance Details" how it is implemented.
If problem persists, please raise a new PMR to discuss with IBM support team.
Or, Query 2 (Planned duration) can be zero, so that's why it cannot be calculated. If so, you need to find out why the data is not retrieved from RTC.
Or, Query 2 (Planned duration) can be zero, so that's why it cannot be calculated. If so, you need to find out why the data is not retrieved from RTC.
Comments
Steve Mulligan
Dec 21 '16, 9:37 a.m.Where in the model are these fields coming from?
vasanthakumari hariharan
Dec 21 '16, 11:28 p.m.Hi,
Steve Mulligan
Dec 22 '16, 4:08 p.m.It is possible that ([Planned_End_Date]-[Planned_Start_Date]) results in zero or NULL for some records. If it does then that may be the problem. You should check for that in your data item expression.
Steve Mulligan
Dec 22 '16, 4:17 p.m.The date in the Request timestamp extension table in the warehouse is datetime. It may not come through the model like that. It is just defined as Value in the model. One thing you could do is to create your own SQL query in the report to get the timestamp extension dates, and get all four dates in one query. That would make it easier to tell what the different values are and then do the calculation in parts. You would then join that to your Request query in the report Query section. If you want examples of that let me know.