Time dimension - group by weeks ending

[Marcin Najgebauer]

Hi,

we have a report that aggregates data by week using a built-in Time dimension Weekly hierarchy.

Currently every aggregated node is the starting date of the period. For instance, we have expandable node for week of Jun 23, 2018 which can be expanded to daily data from range 23-29 June. 

The problem is that the Weekly hierarchy displays nodes as the beginning of the groupped period. Is it possible to get the similar report but with last day in the groupped period? In this case it would be 29 Jun, 2018 which can be expanded to data from range 23-29 Jun.

Thanks in advance

[Ilze Leite-Apine]

Hi Marcin, 

you may want to do the following:

1) create a new measure (in Measures) that returns a weekday number for each day (1  for the first day of the week, 7 for the last):

[Time].CurrentHierarchyMember.get('Week day')

2) Select this measure in report columns (Time dimension Weekly hierarchy should be in rows). For years and weeks, the column would be empty, while the day number would be returned for day level.

3) Order by this column descending and then remove it; the order conditions will remain.

Now, whenever you expand to day level, days will be ordered in a reverse sequence. 

4) Add other measures you need in columns.

Hope it helps!

Ilze, eazyBI support

[Marcin Najgebauer]

I've tested it, and it is only partially what I meant.

The weekdays are indeed ordered from latest to oldest date, but the grouping row is always the first day of the range. I wanted it to be 27 July and 20 July respectively.

[Ilze Leite-Apine]

In eazyBI, Time dimension week members are named by their start date and it can't be changed to the last one. 

In case if you have only a few weeks of interest, you may create a calculated member for each week and name them as you need.

Ilze, support@eazybi.com

[Marcin Najgebauer]

Thanks Ilze, luckily the users understood this limitation and agreed to adjust their approach :-) Thanks for checking!