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

[jwjackso]

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]

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]

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]

Something like attached?

[anders_thorngaa]

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]

maybe helpful:

Creating Reference Dates for Intervals

regards

Marco

[clausbarlose]

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

[clausbarlose]

Hi Anders,

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

[clausbarlose]

Hi Marco

Thanks! - I will look into it!