Hi, all -
I have a Qlikview App that is basically a grid of KPIs. There are three selection boxes, and all of them require only one value to be selected at a time. I want to know how I can set things up so if a user has a value selected in box A, and then selects another in Box B, one of two things will happen:
- If the selection in box A remains possible with the new selection in box B, keep the selection in Box A.
- If the selection in box A is not possible with the new selection in box B, revert to a default value in Box A.
In the attached sample, I want to be able to click the South Region and then, if I click Sharpening, the Region defaults to "Service Line". If i click South Region and click "Wood" or "All" in the department field, I don't want to trigger any behavior in the Region field - it should stay 'South'. The desired behavior would mimic what Qlikview does when you choose mutually exclusive choices - it clears the previously chosen field.
That's the big picture; here's some detail about the specific use case if you want to know more. . Let's pretend we are three factories that make pencils, and we have a Wood, Graphite, and Eraser department in each. The North and Central regions also have a sharpening department, but the south region sends all the pencils to the central region for sharpening.
The user sees a table of various KPIs:
Category / Metric / Value / Target / Variance / YTD Value / YTD Target / YTD Variance / Sparkline
The user controls the content of the grid by choosing from three fields:
Month, Region, Department. Region includes an all inclusive "Service Line" option which is an aggregation of all regions by department, and Department includes an all inclusive "all" option which is an aggregation of all departments by region.
Behind the scenes, there are tables that look like this:
MonthMetrics
Month / Region / Department / Metric / Value
YTDMetrics
Month / Region / Department / YTDValue
MonthTargets
Month / Region / Department / Metric / Target
Because the data is coming from various operational data sources and the metrics use different math to aggregate the data (some are summed, some are averaged), the load script handles the math to aggregate the data from more local to more global. The expressions in the table that the user views just handles the formatting of the KPI numbers. For example, one metric might be sales and so for the "All" Department, we could add the totals for the different departments, but another metric would be a rate of production, where we have a particular numerator to divide by a denominator, which both must be summed and then divided to get the aggregate value for all departments. So in the behind the scenes tables, there are entries for a specific region ('North') and department ('Sharpening') as well as the aggregate metrics ('Service Line' / 'All') and everything in between. Thus, the data model requires one month, one department, and one region to show the user one set of data:
Contents of the MonthMetrics table includes lines like this:
Jun-17 / Service Line / All / Sales / 100
Jun-17 / North / All / Sales / 25
Jun-17 / North / Wood / Sales / 12
Jun-17 / Service Line / Wood / 40
(I don't know why individual manufacturing subunits have their own sales metrics. This is a weird pencil factory, I guess.)
This all seemed like a reasonable data model until I started interacting with my three list boxes, all of which have the "Always One Selected Value" option checked. If in the imaginary scenario above, I have the South region selected (which does not have a Sharpening department), and I then select 'Sharpening', Qlikview won't let me change my department selection to Sharpening until I choose 'North', 'Central', or 'Service Line' for the region (aka, regions that have sharpening metrics). I would like to make my app smarter so that in this scenario, 'Service Line' is automatically chosen when I choose a department that excludes the region I previously selected. However, if I had North selected for my region, and I choose sharpening, I wouldn't want my region to change.
Looking forward to hearing your ideas! Thanks!
