topic Re: how to avoid creating several measures for all dimension values in App Development

how to avoid creating several measures for all dimension values

skyline01 — Sat, 16 Nov 2024 06:21:33 GMT

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:

ID LocationID

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:

LocationID Region

(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)?

Re: how to avoid creating several measures for all dimension values

dplr-rn — Tue, 12 Mar 2019 16:04:44 GMT

Maybe i dont understand the problem completely

Why not just create a line chart with Region as one of the dimensions

and your expression use set analysis to specify the flag and get the percentage

Count({<Flag={'Y'}>}ID)/Count(ID)

if you can share a sample app /data set with desired output i can check further

Re: how to avoid creating several measures for all dimension values

skyline01 — Wed, 13 Mar 2019 19:37:59 GMT

I have attached my qvf file.

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?

Re: how to avoid creating several measures for all dimension values

skyline01 — Thu, 14 Mar 2019 20:59:13 GMT

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:

Count(
	Total <$(Date_Level)>
[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.