Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
skyline01
Contributor

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)
3 Replies
Partner
Partner

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

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
Contributor

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

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
Contributor

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

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.