Where Condition in Load Statement

yashcena — Mon, 02 Jul 2018 08:54:02 GMT

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.

Re: Where Condition in Load Statement

Miguel_Angel_Baeyens — Mon, 02 Jul 2018 09:03:22 GMT

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

Re: Where Condition in Load Statement

ogautier62 — Mon, 02 Jul 2018 09:37:59 GMT

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

topic Re: Where Condition in Load Statement in QlikView

Where Condition in Load Statement

Re: Where Condition in Load Statement

Re: Where Condition in Load Statement