Pivot with nested statement for holidays calendar

orlando162 — Thu, 16 Sep 2021 15:17:00 GMT

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?

Re: Pivot with nested statement for holidays calendar

rubenmarin — Sat, 18 Sep 2021 08:42:59 GMT

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".

topic Pivot with nested statement for holidays calendar in App Development

Pivot with nested statement for holidays calendar

Re: Pivot with nested statement for holidays calendar