Open workorder statistics

[clausbarlose]

Hi – hoping for help on this one!

I have a QV file with workorders:

ID
CreatedDate
ClosedDate

for which I want to calculate how many orders are active on a given date. The definition of an active order is that the CreatedDate >= date in question and date in question <= ClosedDate.

My thought was to generate a new table:

ID
StatDate

- to be able to count the number of IDs on a specific date, but I am having difficulties having this work as reading from one table and autogenerating dates in another table.

Does anyone have a suggestion on how to do this? I have attached an example file with workorders.

BR

Claus

[MarcoWedel · Accepted solution]

one example:

tabWO:
LOAD ID,
    CreatedDate,
    ClosedDate,
    AutoNumberHash128(DayName(CreatedDate),Alt(DayName(ClosedDate),Today())) as IntervalID
FROM [https://community.qlik.com/servlet/JiveServlet/download/1457383-319354/SampleWorkorders.xlsx] (ooxml, embedded labels, table is Sheet1);

tabDates:
LOAD Distinct
    IntervalID,
    DayName(CreatedDate,IterNo()-1) as Date
Resident tabWO
While CreatedDate+IterNo()-1 <= Alt(ClosedDate,Today());

regards

Marco

[clausbarlose]

Hi Marco

Thank you! I've checked a few dates and it seems to compute. I have changed the code a bit to understand it, and reckon that the autonumberhash128 is due to memory conservation?

My question has been answered.

Br,

Claus