sum(aggr( not working

[TomBond77]

Hi experts

I have a table which looks like here:

Finally I would like to get his result on a week basis:

With the following formula I am getting the result of the first table:

(floor(if(Month(WeekEnd(Date)) = Month(Date) , WeekEnd(Date), MonthEnd(Date)))

-

floor(if(Month(WeekStart(Date)) = Month(Date) , WeekStart(Date), MonthStart(Date)))+1)

With the following formula I would like to get the result of the second table. But I am getting no result, only 0 as value is displayed. 

sum(aggr(if (Cap_H_D='24',

(floor(if(Month(WeekEnd(Date)) = Month(Date) , WeekEnd(Date), MonthEnd(Date)))

-

floor(if(Month(WeekStart(Date)) = Month(Date) , WeekStart(Date), MonthStart(Date)))+1)*24, ), Week))

Where is the error in the second formula?

Thanks, Tom

[Or]

I'm not sure I follow. You have a table that already includes the count of days in the week, so your table just needs to be Week and Count of Days In the week? Or count(Distinct Date) with Week being the dimension? Where does the aggr(sum()) come into play?

[TomBond77]

No, the first table shows the result of the first formula, this is a development step to understand the way to go...Yes, I need to count the days of a week.

For week 31 in August 2023 we have 6 days, as the first day of this week 31 belongs to July 2023.

The sum(aggr should come into play to show the days of a week...Is this clear enough?

[Or]

Unfortunately not clear to me. Perhaps it'll be clear to someone else, so I'll bow out and let others look at this.

I don't understand why you're using Sum(Aggr()) here in the first place. You're also referencing a field - Cap_H_D - that isn't in your tables and isn't explained.

[TomBond77]

to be more clear.

I need for each month the count of days in a week. 

There are weeks like week 31 which belongs to two months: July and August.

I would like to count the days per week separately per month. My problem is not the counting of days per week, this has been done with the first formula but without a sum(aggr. Here we see the result on Date basis. In our example week 31 the result is 6 days. 

Now I need to see this result of 6 days in a table without the field Date. I think this is very simple but I am not getting the result of 6 in this example.

[Or · Accepted solution]

I just keep growing more confused... in the bottom table, how is the table meant to know you're only referring to days that are in August? Month isn't a dimension, and every week has seven days even if they are split across multiple months or years. If August has been selected or is a dimension, then just counting the number of distinct dates should get you the result.