Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Zee
Partner - Contributor
Partner - Contributor

Counting Open Orders in a filter selected month with no key date in that month

Counting Open Orders in a given month with no key date in that month: Opened >= MonthEnd(Key_Date) and Closed <= MonthStart(Key_Date)

I am developing a performance reporting dashboard. A key requirement is to show the volume of work as it increases and decreases over time (usually in month periods).
My challenge is: how can I show how many orders were open at any stage in X month? For this example, let's use "Oct 2021". 

Currently, I can count all orders that have been Opened and/or Closed in Oct 2021 when the MonthYear Filter 'Oct 2021' is applied.

But that does not count, for example, an order Opened in Sept 2021 and Closed in Nov 2021. To show the volume of work in Oct 2021, I need to include all Orders that involved resources and activities in that month, even though they have no key date in the month of October 2021.

In other words,
all orders with a 'Date Opened' before or on 31/10/21 (that's 10/31/21 for my American friends)
AND
all orders with a 'Date Closed' after or on 1/10/21 (US format = 10/01/21) OR 'Date Closed' is NULL

This is what a currently have:

Various KPIs
CURRENT ORDERS = current volume of OPEN ORDERS at THIS moment in time

 

=COUNT({<order_status='Open'}>} DISTINCT Order_Ref_No)

 

ORDERS OPENED in X MONTH

 

=Count({<Date_Type={'Date Opened'}>} DISTINCT Order_Ref_No)

 

ORDERS CLOSED in X MONTH

 

=Count({<Date_Type={'Date Closed'}>} DISTINCT Order_Ref_No)

 

A simple bar chart with the dimension of

 

=IF(Key_Date>='01/01/2021', DATE(MONTHEND(Key_Date), 'MMM YYYY'))

 

and 2 measures of ORDERS OPENED and ORDERS CLOSED  as per KPIs above

I would greatly appreciate insight as to how to solve this challenge. Many thanks for your help in advance!

Labels (5)
0 Replies