Is there some way to aggregate attributes ?

[Akaori]

Hello,

I am using tm1py to create dimensions of Date.

Here is a exemple:

Using tm1py I created these two attributes:

• The first attribute refers to weekday
• The second refers to week of month

Is there some way to represent the values from daily to weekly or using week of month ? A kind of consolidation using mdx ?

I was trying to represent the dataset on PAW - Daily, Weekly and Montly using the same dimension of Date, but I had difficult to represent weekly data.

I tried things like that:

{INTERSECT(
FILTER( {TM1SubsetAll([Data])}, SUM([Data].[Ano] = '2019' AND [Data].[Mês] = '01' AND [Data].[Week of Month] = '1'),
FILTER( {TM1SubsetAll([Data])}, SUM([Data].[Ano] = '2019' AND [Data].[Mês] = '01' AND [Data].[Week of Month] = '2'),
FILTER( {TM1SubsetAll([Data])}, SUM([Data].[Ano] = '2019' AND [Data].[Mês] = '01' AND [Data].[Week of Month] = '3')
)}

But it doesn't work.

[MariusWirtz]

Hi @Akaori ,

if you want Consolidations for Weeks (like CalendarWeek or WTD) you should probably create C Elements (like the ones you created for 2019, 2019-01) in the dimension for that.

You can do on-the-fly consolidations in an MDX Query but it's rather verbose and less efficient.

Does that help you ?

[Akaori]

In this example, it`s difficult to consolidate weekdays inside months, because there will be some weeks starting from the previous month.

In this case, working with the second column of attributes (that represents weeks of month - some months have 4, others 5 or 6) sounds good.

Is there some function that transforms these attributes (created using calendar library form python) into a consolidations inside each Monthly Consolidation (1st week, 2nd week, 3rd week, 4th, 5th)?

Thank you for helping me

[Akaori]

Hello Marius,

I found a solution:

for year in years:
     for month in range(1, 13):

         year_month = year + "-" + str(month).zfill(2)
         edges[(year, year_month)] = 1
         semanas = np.where(
             np.array(calendar.monthcalendar(int(year), int(month))) == calendar.monthrange(int(year), int(month))[
                 1])[0][0] + 1

         for i in range(1, semanas + 1):

             v = f'{i}° Semana ({year}-{str(month).zfill(2)})'
             edges[(year_month, v)] = 1

             for j in range(1, (calendar.monthrange(int(year), int(month))[1]) + 1):
                 if (np.where(np.array(calendar.monthcalendar(int(year), int(month))) == j)[0][0] + 1) == i:
                     dia = year + "-" + str(month).zfill(2) + "-" + str(j).zfill(2)
                     edges[(v, dia)] = 1

[MariusWirtz]

Hi @Akaori,

happy to hear that it works!

by the way, are you aware that not all of your consolidations necessarily need to roll up into a single TOTAL Element ?
Like it is totally fine, if you have a dimension like this:

C: TOTAL_Time
C: - 2019
C: -- 2019-M01
N: --- 2019-01-01
...
TOTAL_TIME_BY_WEEKS
C: - 2019_BY_WEEKS
C: -- 2019_W01
N: --- 2019-01-01
...

[Akaori]

Hi @MariusWirtz,

Thank you. I just did this, and worked fine.