Need help on defining calculated member to generate report where week starts on wednesday and ends on tuesday

Questions & Answers
1

I want to generate weekly report ; But my week should be starting from Wednesday and ending next Tuesday (covering 3 months weekly data in single report). Also I don’t want to change week’s definition in “All Heirarachy level members”. But want to add new definition in calculated members. Please guide.

Thanks

2

Hi Paul,

This is a good point and we have added this as a feature for future development - possibility to specify custom hierarchy in Time dimension so that you could have default Week hierarchy and a custom hierarchy, where the week starts, e.g., on Wednesday, in the same account.

Meanwhile, you could create 13 calculated members to aggregate days in weeks starting on Wednesday. One pre-requirement for the following calculation to work is to make sure you have all days in this period in Time dimension - see https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-AddmemberstoTimedimension. If there are all days from three months ago (and e.g. for the whole next year), we can find the last Wednesday correctly and add aggregate the next six days from it correctly in Time dimension calculated member.

So the formula for the first week would be as follows

Aggregate({
  Tail(Filter(
    [Time].[Day].Members,
      DateBetween([Time].CurrentHierarchyMember.StartDate, 'three months ago', 'last wednesday') AND
      [Time].CurrentHierarchy.CurrentMember.get('Week day name') = "Wednesday"
    ), 1).Item(0):
    Tail(Filter(
    [Time].[Day].Members,
      DateBetween([Time].CurrentHierarchyMember.StartDate, 'three months ago', 'last wednesday') AND
      [Time].CurrentHierarchy.CurrentMember.get('Week day name') = "Wednesday"
    ), 1).Item(0).Lead(6)
 })

Then for next week you only change the number in Tail(…) function (used in two places in the formula) to specify which Wednesday from the list of Wednesdays to take as the starting point for the ‘week’. So the change for week 2 would be

Aggregate({
  Tail(Filter(
    [Time].[Day].Members,
      DateBetween([Time].CurrentHierarchyMember.StartDate, 'three months ago', 'last wednesday') AND
      [Time].CurrentHierarchy.CurrentMember.get('Week day name') = "Wednesday"
    ), 2).Item(0):
    Tail(Filter(
    [Time].[Day].Members,
      DateBetween([Time].CurrentHierarchyMember.StartDate, 'three months ago', 'last wednesday') AND
      [Time].CurrentHierarchy.CurrentMember.get('Week day name') = "Wednesday"
    ), 2).Item(0).Lead(6)
 })

Lauma / support@eazybi.com

3

Hi @Prabhu ,
I wanted to let you know that we have released eazyBI version 7.0 recently. We included in this version an option to define custom time hierarchies . See the documentation for different examples: Custom time hierarchies

Please see a list of all changes: Changelog - eazyBI for Jira.

best,
Gerda // support@eazybi.com