Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
skyline01
Creator
Creator

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:

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

Labels (1)
  • Chart

3 Replies
dplr-rn
Partner
Partner

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

skyline01
Creator
Creator
Author

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?

skyline01
Creator
Creator
Author

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.