Time logged on previous workday

Questions & Answers
1

So my question seems to be quite simple but I’m either not searching for the right thing or it’s so simple no-one ever needed to ask about it. Anyway, I wish to show hours logged on yesterday, or on Friday if today is Monday. So basically time logged on previous working day. I managed to get time logged on yesterday but I can’t seem to figure out how to create IF statement “if today is monday then get time logged on last friday”.

Any pointers?

2

Hi @JurgenT

Very good question.

In that case, you could try creating a calculated measure using this code:

CASE WHEN
[Time].CurrentHierarchyMember.Level.Name = "Day"
THEN
  CASE WHEN
  [Time].CurrentHierarchyMember.Key = 1 --key 1 means Monday
  THEN
    Aggregate(
    [Time].CurrentHierarchyMember.Lag(3), --returns member 3 days ago
    [Measures].[Hours spent]
  )
  ELSE --for all other days of week
  Aggregate(
    [Time].CurrentHierarchyMember.PrevMember,
    [Measures].[Hours spent]
  )
  END
END

Martins / eazyBI support

3

Hi,

Thanks for your input! I can’t seem to get it working. I created calculater measure as you said and in the “Rows” section I have “Logged By”. All the values are blank:
image

4

The code above expected that you use “Time” dimension in the report and select the day level
Try extending the logic to cover cases where “Time” dimension is not used in the report (then it would always look at today date and find the previous workday vs. today).

CASE WHEN
[Time].CurrentHierarchyMember.Level.Name = "Day"
THEN
  CASE WHEN
  [Time].CurrentHierarchyMember.Key = 1
  THEN
    Aggregate(
    [Time].CurrentHierarchyMember.Lag(3),
    [Measures].[Hours spent]
  )
  ELSE
  Aggregate(
    [Time].CurrentHierarchyMember.PrevMember,
    [Measures].[Hours spent]
  )
  END
WHEN
[Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember --Time dimension not used or all times selected
THEN
  CASE WHEN
  [Time].[Day].CurrentDateMember.key = 1 
  THEN
    Aggregate(
    [Time].[Day].CurrentDateMember.Lag(3),
    [Measures].[Hours spent]
  )
  ELSE
  Aggregate(
    [Time].[Day].CurrentDateMember.PrevMember,
    [Measures].[Hours spent]
  )
  END
END

CurrentDateMember function always returns today’s date.

Martins

5

Hi!

Thanks again. It works as needed, however I still would like to understand why the first version doesn’t work for me.

So I created new calculated measure in “Measures” dimension and it is location in Columns section. In Rows section i have “Logged by” and “Time” dimensions. In “Time” dimension, when I select “All hierarchy level members” -> “Select all members at level” -> “Day”, it shows all days from the beginning of Jira usage for us (so 900 rows).

What am I doing wrong?

image
image268×516 9.18 KB

6

If you have two dimensions in rows area and you don’t enable “Nonempty” cross join button for row dimensions, then all the possible combinations from two dimensions would be displayed in your report unless you filter the report by some measure or hide empty results.

Martins / eazyBI

7

Hi,

Hiding empty rows didn’t change the problem I’m facing. Please see the image below, maybe you can point me in the right direction:

image
image1116×1011 81.1 KB

8

Hi,

In the previous images you didn’t use “Time” dimension in report rows so this when I assumed that “Time” would not be used in rows, and the code I shared earlier is not expecting “Time” dimension in rows to be honest.
Do you want to always return the last workday (vs. today - the day when you run the report) OR do you expect to see dynamically the previous day for the displayed date?

Martins

9

Hi,

I always want to return last workday vs today. I’ll be looking at this report every morning to see time logged on previous workday. Was your first code snippet meant to work for the other use case - for displaying time logged for previous workday of the displayed date?

Jürgen

10

Hi,

I am sorry for the misunderstanding.
If you don’t use the “Time” dimension in your report, the last code snippet should work as expected.

Martins

11

Indeed it does! Thanks!

12

Hi,

Since it is actually monday today, I got a change to test your snippet. It turns out it doesn’t work as expected.
“[Time].[Day].CurrentDateMember.Key” doesn’t equal to 1, but equals to 27 which is the date of today.

How should I modify the snippet so I could get current day of week?

13

Hi,

You are right, it would return the key of today’s date.

Try this code for your calculated measure instead:

CASE
WHEN
[Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember --Time dimension not used or all times selected
THEN

  CASE WHEN
  [Time].[Day].CurrentDateMember.get('Week day name') = "Monday"
  THEN
    Aggregate(
    [Time].[Day].CurrentDateMember.Lag(3),
    [Measures].[Hours spent]
  )
  ELSE
  Aggregate(
    [Time].[Day].CurrentDateMember.PrevMember,
    [Measures].[Hours spent]
  )
  END
END

I made it shorter now since you don’t use “Time” dimension in your report.

Martins / eazyBI support

14

Excellent, this is now working!