How to calculate sum of hours for last 12 periods worked for each worker

[#1]

Hi,

Can anyone help me with the following question?

I need to calculate the SUM of the last 12 rows from the "Hours_This_Period" column for each "Personnel_Ref", based on the "Tax_Year" and "Tax_Period".

I have attached a spreadsheet with a sample of the data.

What i am to get is something like this:

Personnel_ref     Sum_of_Hours

1107405                         144

1107410                           43

1107500                           90

I would appreciate any help given.

Thank you,

Shane

[sunny_talwar · Accepted solution]

May be like this:

=Sum(Aggr(If(Tax_Date >= Alt(Max(TOTAL <Personnel_Ref> Tax_Date, 12), 0), Hours_This_Period), Tax_Date, Personnel_Ref))

Script:

Table:

LOAD Personnel_Ref,

  MakeWeekDate(Tax_Year, Tax_Period) as Tax_Date, 

     Tax_Year,

     Tax_Period,

     Hours_This_Period

FROM

Hours.xlsx

(ooxml, embedded labels, table is Sheet1);

[maxgro]

script

Table:

LOAD Personnel_Ref,

  MakeWeekDate(Tax_Year, Tax_Period) as Tax_Date, 

     Tax_Year,

     Tax_Period,

     Hours_This_Period

FROM

Hours.xlsx

(ooxml, embedded labels, table is Sheet1);

Table2:

load

  *,

  if(Personnel_Ref = Peek('Personnel_Ref'), Peek('Sequence') +1, 1) as Sequence

Resident Table

order by Personnel_Ref, Tax_Year desc, Tax_Period desc;

DROP Table Table;

chart

dimension      Personnel_Ref

expression     =Sum({$ <Sequence={"<=12"}>} Hours_This_Period)

[#4]

Thank you Sunny T, your answer worked perfectly.

Regards,

Shane

[#5]

Hi Massimo,

Your answer worked too. Thanks for helping.

Regards,

Shane