topic Re: Open workorder statistics in QlikView

Open workorder statistics

clausbarlose — Thu, 22 Mar 2018 08:57:47 GMT

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.

Claus

Re: Open workorder statistics

jwjackso — Thu, 22 Mar 2018 11:21:35 GMT

Have you tried using set analysis, https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-analysis-expressio…

Count({$<Date={">=CreatedDate<=ClosedDate"}>}ID)

Will the ClosedDate always be populated or can it be null?

Re: Open workorder statistics

clausbarlose — Thu, 22 Mar 2018 14:16:32 GMT

Hi Jerry

Unfortunately no - will have to dive into it, though. At the moment I'm not using a master calender in my QV as this is an older code.

Isn't it possible to do it the way I suggest - generating a new table with autogenerated dates from another table?

Den 22. marts 2018 kl. 12.22.21 +01.00, skrev Jerry Jackson <qcwebmaster@qlikview.com>:

Qlik Community <https://community.qlik.com/?et=watches.email.thread>
Open workorder statistics
reply from Jerry Jackson <https://community.qlik.com/people/jwjackso?et=watches.email.thread> in Scripting - View the full discussion <https://community.qlik.com/message/1457552?et=watches.email.thread#1457552>
Have you tried using set analysis, https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-analysis-expressio… <https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-analysis-expressions.htm>
Count({$<Date={">=CreatedDate<=ClosedDate"}>}ID)
Will the ClosedDate always be populated or can it be null?
Reply to this message by replying to this email, or go to the message on Qlik Community <https://community.qlik.com/message/1457552?et=watches.email.thread#1457552>Start a new discussion in Scripting by email <discussions-community-qlikview-scripting@qliktech-public-v7.hosted.jivesoftware.com> or at Qlik Community <https://community.qlik.com/choose-container.jspa?contentType=1&containerType=14&container=2061&et=watches.email.thread>Following Open workorder statistics <https://community.qlik.com/message/1457552?et=watches.email.thread#1457552> in these streams: Inbox

Re: Open workorder statistics

jwjackso — Thu, 22 Mar 2018 14:32:05 GMT

If you are only interested in the count relative to today, I think creating a new table or flag would work. But I don't think that helps you with dynamically picking a date unless you create the table based on all possible dates.

Re: Open workorder statistics

sasiparupudi1 — Thu, 22 Mar 2018 15:05:52 GMT

Something like attached?

Re: Open workorder statistics

anders_thorngaa — Thu, 22 Mar 2018 15:56:51 GMT

Hi Claus,

I have defined a variable called vMyDate.

Try inserting the following formula (assuming vMyDate >=CreatedData and vMyDate either < ClosedDate or ClosedDate = Null):

=Count({<CreatedDate={"<=$(vMyDate)"},ClosedDate={">$(vMyDate)"}>}ID)

+Count({<CreatedDate={"<=$(vMyDate)"},ClosedDate=>}ID)

-Count({<CreatedDate={"<=$(vMyDate)"},ClosedDate={'*'}>}ID)

Re: Open workorder statistics

MarcoWedel — Thu, 22 Mar 2018 21:40:12 GMT

maybe helpful:

Creating Reference Dates for Intervals

regards

Marco

Re: Open workorder statistics

clausbarlose — Fri, 23 Mar 2018 07:50:55 GMT

Thank! - I will check my data and post a reply, please don't be offended if it takes a couple of days!

Re: Open workorder statistics

clausbarlose — Fri, 23 Mar 2018 07:51:43 GMT

Hi Anders,

Thanks - I'll try it out. Please don't be offended if it takes a few days!

Re: Open workorder statistics

clausbarlose — Fri, 23 Mar 2018 07:51:59 GMT

Hi Marco

Thanks! - I will look into it!

Re: Open workorder statistics

MarcoWedel — Fri, 23 Mar 2018 15:31:59 GMT

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

Re: Open workorder statistics

clausbarlose — Mon, 26 Mar 2018 21:40:57 GMT

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