I am looking for a method to find the first time staff entered time into Tempo. I’m doing this in lieu of brining in their HR Start date from another system. It will provide a date that is “good enough” for the report I’m generating.
Can anyone help with this? It’s important for me this next week.
Hi @TBooth ,
If you want to find the first date a person logged hours in Tempo, you might iterate through the Time dimension to see the first date having logged hours.
However, that might be a lengthy process if you have multiple users and long history imported.
You might optimize the expression to iterate through the quarter level of the Time dimension and then through the days of that quarter only.
The expression might then be as follows.
I added the comments to show the order of query execution.
--0) condition to check if the context is relevant
CASE WHEN
[Measures].[Hours spent]>0
THEN
--7) take the first day with logged hours
Head(
--6) filter the days of the quarter with logged hours
Filter(
--5) retrieve the days from the first quarter
DescendantsSet(
--3) take the first quarter from the filtered set
Head(
Filter(
--1) take set of all quarters
[Time].[Quarter].Members,
--2) filter conditions - hours were logged
[Measures].[Hours spent]>0),
-- 3) number of quarter retrieved
1
--4) address the filtered quarter
).Item(0),
--5) retrieve the descendants of quarter at day level
[Time].[Day]),
--6) condition for day - has logged hours
[Measures].[Hours spent]>0),
--7) take first member of filtered set
1
--8) address the first day and take its start date
).Item(0).StartDate
END
Regards,
Oskars / support@eazyBI.com
Oskars - thank you for your help with this.
The intent of my report is to identify staff that are charging more than 3 hours to a Tempo code (New Role Ramp Time) in the previous week. When I added this to the user defined field, it seems only to provide the first time in the Previous Week, when what I am looking for in this report is the first day that staff reported time in Tempo even if it is not in the previous week. Does that make sense?
Below is the Report Definition if that helps:
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Staff with more than 3 hours in New Role Ramp Time in the previous week",
"folder_name": "Time Tracking Reports",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Hours spent]","[Measures].[Logged by TeamName]","[Measures].[Logged by RoleName]","[Measures].[First Tempo Date]"],"members":[]},{"name":"Time","selected_set":["[Time.Weekly].[Previous week]"],"members":[],"bookmarked_members":[]}]},"rows":{"dimensions":[{"name":"Issue","selected_set":["[Issue].[Internal Time Logs].[INTERNAL-12 New Role Ramp Time - If you are new to your project]"],"members":[],"bookmarked_members":[]},{"name":"Logged by","selected_set":["[Logged by].[All Users]"],"members":[{"depth":0,"name":"All Users","full_name":"[Logged by].[All Users]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[]}],"order_by":{"expression":["[Measures].[Logged by TeamName]","[Time.Weekly].[Previous week]"],"order":"ASC"},"filter_by":{"conditions":[{"expression":["[Measures].[Hours spent]","[Time.Weekly].[Previous week]"],"operator":"\u003e","value":"3"}]},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Issue","duplicate":true,"selected_set":["[Issue].[All Issues]"],"members":[{"depth":0,"name":"All Issues","full_name":"[Issue].[All Issues]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Internal Time Logs","full_name":"[Issue].[Internal Time Logs]","drillable":true,"key":"INTERNAL","expanded":true,"drilled_into":false,"parent_full_name":"[Issue].[All Issues]"},{"depth":2,"name":"INTERNAL-12 New Role Ramp Time - If you are new to your project","full_name":"[Issue].[Internal Time Logs].[INTERNAL-12 New Role Ramp Time - If you are new to your project]","key":"INTERNAL-12","parent_full_name":"[Issue].[Internal Time Logs]"}],"bookmarked_members":[],"current_page_members":["[Issue].[Internal Time Logs].[INTERNAL-12 New Role Ramp Time - If you are new to your project]"]}]},"options":{"nonempty":"rows"},"view":{"current":"table","maximized":false,"table":{"freeze_header":true}},"calculated_members":[]}
} ],
"calculated_members": [{"dimension":"Time","name":"Previous week","format_string":"","formula":"Aggregate({\n [Time.Weekly].[Week].CurrentDateMember.PrevMember\n})","dimension_hierarchy":"Weekly"},{"name":"Logged by RoleName","dimension":"Measures","formula":"[Logged by].CurrentHierarchyMember.get('RoleName')","format_string":""},{"name":"Logged by TeamName","dimension":"Measures","formula":"[Logged by].CurrentHierarchyMember.get('TeamName')","format_string":""},{"name":"First Tempo Date","dimension":"Measures","formula":"CASE WHEN\n[Measures].[Hours spent]\u003e0\nTHEN\n--7) take the first day with logged hours\n Head(\n--6) filter the days of the quarter with logged hours \n Filter(\n--5) retrieve the days from the first quarter \n DescendantsSet(\n --3) take the first quarter from the filtered set\n Head(\n Filter(\n --1) take set of all quarters\n [Time].Members,\n --2) filter conditions - hours were logged \n [Measures].[Hours spent]\u003e0),\n-- 3) number of quarter retrieved\n 1\n--4) address the filtered quarter \n ).Item(0),\n--5) retrieve the descendants of quarter at day level \n [Time].[Day]),\n--6) condition for day - has logged hours \n [Measures].[Hours spent]\u003e0),\n--7) take first member of filtered set\n 1\n--8) address the first day and take its start date \n ).Item(0).StartDate\nEND","format_string":"mmm dd yyyy"}]
}
Hi @TBooth,
You had a filter on the Time dimension weekly hierarchy. The iteration through the Time dimension default hierarchy did not change that. Therefore, the measures within conditions were impacted by the current page selection.
The solution is to reset the irrelevant page filters. In this case, that is only the “Time.Weekly” hierarchy.
The updated expression might be as follows.
CASE WHEN
[Measures].[Hours spent]>0
THEN
Head(
Filter(
DescendantsSet(
Head(
Filter(
[Time].[Quarter].Members,
--filter conditions - hours were logged, resetting Weekly hierarchy
([Measures].[Hours spent],
[Time.Weekly].DefaultMember)>0),
1).Item(0),
[Time].[Day]),
-- condition for day - has logged hours, reset weekly hierarchy
([Measures].[Hours spent],
[Time.Weekly].DefaultMember)>0),
1).Item(0).StartDate
END
Regards,
Oskars / support@eazyBI.com