I am working in Qlik Sense Enterprise. I know that the question I’m about to ask and the scenario that I’m in suffers from many design problems, but there is nothing I can do about that. That being said, I have an app enhancement request that I don’t know how to solve, that I’d appreciate some help with.
I have inherited a Qlik app that displays data about tickets. The tickets capture attributes like ticket ID, applications affected, locations affected, and time (date of occurrence). The Qlik app computes some measure (call it “availability”) per location, per month (by data of occurrence), and per application affected. “Availability” is computed as a percentage of time that the given application was available for a given time period.
The app only has 1 sheet, and its layout is as follows:
-a bunch of buttons that lets the user pick a certain location
-1 table chart with the following columns:
Application (dimension)
February (measure)
March (measure)
April (measure)
May (measure)
June (measure)
July (measure)
August (measure)
September (measure)
October (measure)
November (measure)
December (measure)
January (measure)
Yes, the time dimension has been split up into individual measure columns, which is incredibly bad design (at least, in my opinion). Furthermore, only the applications relevant for a given location are to be displayed in this table. So, the Application dimension is dynamic. (I think this is bad design as well.)
The app sets the following variables in the load script:
vApplicationsValueList
=ValueList(
If( vLocation = ‘Loc1’
or vLocation = ‘Loc2’
,’App1’
)
,If( vLocation = ‘Loc3’
or vLocation = ‘Loc4’
,’App2’
)
,’App3’
)
vApplications
= ‘App1’, ‘App2’, ‘App3’
The code behind the visualizations is as follows:
-location buttons:
As an example, the “Loc1” button has action:
set variable value
variable name: vLocation
value: ‘Loc1’
-table chart
Application
=ValueList(
If( vLocation = ‘Loc1’
or vLocation = ‘Loc2’
,’App1’
)
,If( vLocation = ‘Loc3’
or vLocation = ‘Loc4’
,’App2’
)
,’App3’
)
As an example of one of the monthly measures, here is the ‘July’ measure definition:
=(
Count( {$<FiscalYear = {‘2019’}, Month = {‘July’}>} Days)
-
Pick(
Match(
$(vApplicationsValueList)
,$(vApplications)
)
,Sum({$<FiscalYear = {‘2019’}, Month = {‘July’}, Location = {$(vLocation)}, Application = {‘App1’} Duration)
,Sum({$<FiscalYear = {‘2019’}, Month = {‘July’}, Location = {$(vLocation)}, Application = {‘App2’} Duration)
,Sum({$<FiscalYear = {‘2019’}, Month = {‘July’}, Location = {$(vLocation)}, Application = {‘App3’} Duration)
)
/
Count( {$<FiscalYear = {‘2019’}, Month = {‘July’}>} Days)
This same code is repeated for the other monthly measure columns, but with ‘Month’ updated accordingly.
Notice that the order of the Sum expressions in this measure definition has to match the order of the applications listed in vApplications. Also note that the Application synthetic dimension’s definition has to match that of vApplicationsValueList.
I have been requested to enhance this Qlik app. The user wants the availability goal displayed next to each application in the table. The availability goal is defined per location, per application. I have a matrix that lists these goals (percentage values), but I am open on how to implement them. How can I implement a column to the right of the Application dimension column that can show these availability goals? Because of the Application column being synthetic, and because the measure columns utilize a pick-match, I imagine that this new column will have to use something like a nested pick-match, but I’m not sure. Thanks in advance.
