topic Re: Running count of open incidents over time in QlikView

Running count of open incidents over time

arixooo123 — Mon, 16 Apr 2018 22:41:31 GMT

am trying to count number of Open Events in every month. The problem is my current expression counts the open ones even for the months prior the creation date of the event.

Dimension: MM-YYY

Expression:

  Count( total {<[Fin Yr]=,[Mth]=,[Day Number Of Month],[FBC_Status]={'Open'}, DueDate={"<=$(=Today())"},

I added this part in order to make sure the OpenDate of the event is less than LastDayOfTheMonth.

  OpenDate={"<=LastDayOfTheMonth"}>} ID)

LastDayOfTheMonth is a field in my Calendar table, so for Sep-2017 the value is 30/09/2017.

But since the IDs are connected to Calendar with Date Key, every ID has is associated with a LastDayOfTheMonth of its own date key and I can't filter it based on Dimension value.

Consider in Below table on Month Sep-2017, How can I make sure the expression is counting the IDs being opened before 30/09/2017?

If I define a variable as LastDayOfTheMonth, would it act differently for different months in the dimension ?

Thanks

Re: Running count of open incidents over time

YoussefBelloum — Mon, 16 Apr 2018 23:24:05 GMT

Hi,

1. would be able to share your table structure / sample file / sample app ?

2. how do you want it to be dynamic (now limited to sept.2017 for test purposes) ?

Re: Running count of open incidents over time

arixooo123 — Mon, 16 Apr 2018 23:50:46 GMT

Hi Yousef,

Please find the Sample in the attachment.

In Total Due expression:

Taking September 2017 as an example, I want to make sure I am just counting the all the incidents that are closed and created in that months plus those incidents that are created in prior months but where overdue in Sep 2017.

Unfortunately the current expression counts currently overdue incidents for all the months (They need to be counted only for months after they became overdue)

I know it's confusing, sorry

Re: Running count of open incidents over time

arixooo123 — Wed, 18 Apr 2018 03:29:23 GMT

I changed the expression to below and it seems that it's working:

RangeSum(above(Count( {<[Fin Yr]=,[Mth]=,[Day Number Of Month],[FBC_Status]={'Open'},DueDate={"<=$(=Today())"}>} Comp_ID),0,RowNo()))

Re: Running count of open incidents over time

YoussefBelloum — Wed, 18 Apr 2018 08:43:18 GMT

Good, so don't need to take a look