Where Condition in Load Statement

[yashcena]

I have a load script in which i'm calculating the average hours over last 26 weeks (~6 months).

  LOAD

        [Resource_CUID] as [tResource_CUID],     // Key

        (Sum([Hours]) / 26) As [tHours]

    RESIDENT

        TimeTracking

    WHERE

        [WorkDt_Date] >= (Today() - (26*7))

    GROUP BY

        [Resource_CUID]

;

I have a column for Project Completion Date in my data model.

My requirement is to calculate the average hours for the last 26 weeks but when a project which has started after 26 weeks , the average should be calculated for that period only.

26 weeks into past is January 2018.

So, for example, if a project has started in May 2018, then it's average should be calculated from May and June (8 weeks only) and not over the entire 26-week window.

For a project that started in March, the avergae should bve calculated for March, April, May, June (16 weeks only) and not over 26-weeks.

I'm not able to get orrect syntax for where clause.

I hope I have made the requirement clear.

Any Help would be appreciated.

[Miguel_Angel_Baeyens]

The script looks fine, but if the format of WorkDt_Date is not numeric in the format of (Today() - 26*7), which will be a numeric value like 43097 (and not DD/MM/YYYY or similar).

[ogautier62]

Hi,

you can't divide by 26, but by the exact number of weeks for each resource

so :

sum(hours) / ( today() - min([WorkDt_Date]) * 7

regards