Forum Discussion
Solved
Error in calculating Duration in direct query
Hello Everyone, I have a requirement where I have direct connection from SAP HANA for Cube views. In one of the view, I have start date, end date, start time and end time. As this is direct quer...
After so many trials I have found out the solution for this.
created new column =
1. Approval sent date = DATEVALUE([Field of date])
2. Approval sent time = TIMEVALUE([Field of time])3. Approved Date = DATEVALUE([Field of date.1])4. Approved Time = TIMEVALUE([Field of time.1])5. Duration in dates = DATEDIFF(FIRSTDATE('CA_ZSAF_APPROVALS'[Approval sent date]),FIRSTDATE('CA_ZSAF_APPROVALS'[Approved Date]),HOUR)6. Duration in Time = DATEDIFF(FIRSTNONBLANK('CA_ZSAF_APPROVALS'[Approval sent time],('CA_ZSAF_APPROVALS'[Approval sent time] == 'CA_ZSAF_APPROVALS'[Approval sent time])),FIRSTNONBLANK('CA_ZSAF_APPROVALS'[Approved Time],('CA_ZSAF_APPROVALS'[Approved Time] == 'CA_ZSAF_APPROVALS'[Approved Time])),HOUR)7. Final Duration in Hours = IF('CA_ZSAF_APPROVALS'[Duration in dates] >= 0, [Duration in dates] + [Duration in Time], [Duration in dates] - [Duration in Time] )Still it has many modifications to do but at least it is showing me duration in hours and some changes will show correct values.
After so many trials I have found out the solution for this.
created new column =
1. Approval sent date = DATEVALUE([Field of date])
2. Approval sent time = TIMEVALUE([Field of time])
3. Approved Date = DATEVALUE([Field of date.1])
4. Approved Time = TIMEVALUE([Field of time.1])
5. Duration in dates = DATEDIFF(FIRSTDATE('CA_ZSAF_APPROVALS'[Approval sent date]),FIRSTDATE('CA_ZSAF_APPROVALS'[Approved Date]),HOUR)
6. Duration in Time = DATEDIFF(FIRSTNONBLANK('CA_ZSAF_APPROVALS'[Approval sent time],('CA_ZSAF_APPROVALS'[Approval sent time] == 'CA_ZSAF_APPROVALS'[Approval sent time])),FIRSTNONBLANK('CA_ZSAF_APPROVALS'[Approved Time],('CA_ZSAF_APPROVALS'[Approved Time] == 'CA_ZSAF_APPROVALS'[Approved Time])),HOUR)
7. Final Duration in Hours = IF('CA_ZSAF_APPROVALS'[Duration in dates] >= 0, [Duration in dates] + [Duration in Time], [Duration in dates] - [Duration in Time] )
Still it has many modifications to do but at least it is showing me duration in hours and some changes will show correct values.