Solved: Open workorder statistics - Qlik Community

clausbarlose · ‎2018-03-22

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 · ‎2018-03-23

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

View solution in original post

jwjackso · ‎2018-03-22

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

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

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

clausbarlose · ‎2018-03-22

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

jwjackso · ‎2018-03-22

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.

sasiparupudi1 · ‎2018-03-22

Something like attached?

anders_thorngaa · ‎2018-03-22

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)

MarcoWedel · ‎2018-03-22

maybe helpful:

Creating Reference Dates for Intervals

regards

Marco

clausbarlose · ‎2018-03-23

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

clausbarlose · ‎2018-03-23

Hi Anders,

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

clausbarlose · ‎2018-03-23

Hi Marco

Thanks! - I will look into it!