Pivot with nested statement for holidays calendar

[orlando162]

Dear all

I'm having some trouble with a pivot table.

This is the nested statement I created to sum all the working hours on timesheet registered during "holidays":

if((Day='1/1/2021' or Day='6/1/2021' or Day='19/3/2021' or Day='5/4/2021' or Day='1/5/2021' or Day='13/5/2021' or Day='24/5/2021' or Day='3/6/2021'
or Day='29/6/2021' or Day='1/8/2021' or Day='15/8/2021' or Day='1/11/2021' or Day='8/12/2021' or Day='25/12/2021' or Day='26/12/2021' or
Day='1/1/2022' or Day='6/1/2022' or Day='19/3/2022' or Day='18/4/2022' or Day='1/5/2022' or Day='26/5/2022' or Day='6/6/2022' or Day='16/6/2022'
or Day='29/6/2022' or Day='1/8/2022' or Day='15/8/2022' or Day='1/11/2022' or Day='8/12/2022' or Day='25/12/2022' or Day='26/12/2022'
or Weekday(Day)='Sun' or Weekday(Day)='Sat')
and ([Risorse.Company]='Arch SA' or [Risorse.Company]='SOURCE'),
(Sum(overtime)+Sum(travel)+Sum(night_shift)+Sum(internal)),Null())

If I paste this formula in a straight table I am able to see "holidays working hours" (worked during holidays). But if I try to create a pivot table to summarize weekly "holidays working hours" it doesn't work.

I tried Sum(Aggr(...), Employee)       or 

Sum(Aggr(...), Week(Day))       or

Sum(Total(...)

but nothing.

Someone can help me please?

[rubenmarin]

Hi, its probaly a data model issue but it's hard to say without loking at it, or at least a sample to make some tests.

BTW, instead of "Sum(Aggr(...), Week(Day))", create a Week field in script like "Week(Day) as Week" and use "Sum(Aggr(...), Week)". If you show different years maybe you need a WeekYearField "Week(Day)&'/'&Year(Day) as WeekYear".