How to create Day level hours spent report by Month and weeks Filter

Questions & Answers
1

Hi All ,

I am working on creating Hours spent report. My requirement is to be filter the result by Months and Weeks.

Report configuration

Rows : Logged by , Time (Day Level) Dimensions
Columns : Hours Spent measure

Pages : time

Now , I want to display current month, current week, Previous month and previous week Filters.

I tired creating calculated members on time dimension for current week and previous week. when I select “Day” Time Hierarchy , Previous week and Current week filter doesn’t displayed the result.

Could anyone help me is there any way to overcome this problem.

Thanks and let me know if any additional information required.

2

eazyBI supports usage of one hierarchy of a dimension in a report. Therefore you can use either Weekly selections or Monthly selections there.

There is a workaround that you can create calculated members in default hierarchy to aggregate days on current week, or previous week.

For the example below I am using WeekDay function. This function will retrieve me 1 for Monday, and 7 for Sunday. I am also using DateAdd function to move this date based on a weekday to the first date of the week and the last date of the week. I am using arithmetical calculations to get the first and last day the of week based on todays weekday value.

Here is a formula for calculated member Current week:

Aggregate(
  {
  [Time].[Day].DateMember(DateAdd('d',(WeekDay(Now(),2)-1)*-1,Now())):
  [Time].[Day].DateMember(DateAdd('d',7-WeekDay(Now(),2),Now()))
  }
)

Here is a formula for calculated member Previous week:

Aggregate(
  {
  [Time].[Day].DateMember(DateAdd('d',(WeekDay(Now(),2)+6)*-1,Now())):
  [Time].[Day].DateMember(DateAdd('d',WeekDay(Now(),2)*-1,Now()))
  }
)

Daina / support@eazybi.com

1 Like
3

Thanks Daina, it works for us