how to avoid creating several measures for all dimension values
I have a 3-table data model. Table 1 holds ticket data, with structure like
ID DateCreated Flag
Table 2 holds all of the locations per ticket, with structure like:
There is a one-to-many relationship from Table 1 to Table 2 (i.e., a single ticket can have many locations).
Table 3 holds "lookup data" for all locations, with structure like:
(So, there is a many-to-one relationship from Table 2 to Table 3.)
I want to create a line chart of % of tickets over time, by Region. % of tickets = number of tickets where Flag = 'Yes' / total number of tickets. Time is DateCreated. I know that I can create this by creating several measures in my line chart, 1 per Region value. Currently, it's do-able, since there are only about 5 distinct Region values. However, I'd like to avoid this hard-coding approach. Is there another way to do this without hard-coding (i.e., not create several measures)?
Note: I denormalized the location dimension into the "locations impacted" table. (I'm not sure if this affects the solution.)
Also, I want to do this not just by region, but by change request type as well. Can you please prescribe a solution for that as well? (I'm not sure if the solution is different for each, since locations are on a separate table, whereas change request type is on the main fact table.)
My bottom visualizations are correct, but they are by the multiple measures solution method, which I don't like because it's all hard-coding dimension values. (What if my dimension values change?) My top visualizations are clearly wrong, but I want their solution method (i.e., 2 dimensions, 1 measure). How can I correct my top visualizations to look like the bottom visualizations?
I figured out the solution to my problem. I want my "percent affected" measure to be:
number of tickets created (for the given month, for the given ticket type) / number of tickets created (for the given month)
Because I have 2 dimensions (and 1 measure), my denominator is effectively the same as my numerator (explaining why all values are 100 %). So, I had to tell the denominator to "ignore" the "ticket type" dimension. I achieved this with the TOTAL parameter, as follows:
[Major Incident ID]
$(Date_Level) is how I trick Qlik Sense into using my master drilldown dimension, as you can't really use master dimensions in expressions / measures in Qlik Sense.