Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arixooo123
Creator III
Creator III

Running count of open incidents over time

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

trend.png

1 Solution

Accepted Solutions
arixooo123
Creator III
Creator III
Author

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()))

View solution in original post

4 Replies
YoussefBelloum
Champion
Champion

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) ?

arixooo123
Creator III
Creator III
Author

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

arixooo123
Creator III
Creator III
Author

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()))

YoussefBelloum
Champion
Champion

Good, so don't need to take a look