I have the following scenario in qlikview, in which I need some help.
There are requests identified with a unique id (REQUEST_ID). The requests can be of two types (TYPE_CREATION or TYPE_ACTION). Action type requests are connected to several actions (ACTION_ID) and each action is connected to a team (TEAM) and a status (STATUS). Any request whose latest action at a selected date is not closed or archived is considered active.
I wanted to count the number of active requests and with some help on another discussion I got to this expression that does it.
count({$<REQUEST_ID = {"=not match(FirstSortedValue(STATUS,-ACTION_ID),'Closed','Archived')"},>} distinct REQUEST_ID)
However I wanted to display this count in a bar chart using a date as Dimension, and I wanted the date that identifies the active requests to be the date connected to TYPE_CREATION instead of the date connected to Actions.
So I tried this expression, that seemed to solve the problem.
count({<TYPE={'Type_Creation'}>}aggr(count({$<REQUEST_ID = {"=not match(FirstSortedValue(STATUS,- ACTION_ID), 'Closed', 'Archived') "},TYPE={'Type_Creation','Type_Action'}>} distinct REQUEST_ID), REQUEST_ID))
However as a last requirement I needed to show all that were active at a selected date, ignoring the year and month selections for values lower than the currently selected date. So I added this to the expression
YEAR=,MONTH=,DATE={"<=$(vSelectedDate)"}
And I got this final expression:
count({<TYPE={'Type_Creation'},YEAR=,MONTH=,DATE={"<=$(vSelectedDate)"}>}aggr(count({$<REQUEST_ID = {"=not match(FirstSortedValue(STATUS,- ACTION_ID), 'Closed','Archived')"},TYPE={'Type_Creation','Type_Action'},YEAR=,MONTH=,DATE={"<=$(vSelectedDate)"}>} distinct REQUEST_ID), REQUEST_ID))
This seems to work fine when I see the graph with an year selected, but when I select a month, the number of active requests before the selected month becomes 0.
Any idea on what could be wrong in the expression, or another way of doing this graph?
Thank you in advance.
Best regards
