I want to create a measure for Cost per Person. I have two tables: People and Expenses. The key that links them is period_person. We track the status or attributes of each person in every period. I'd like to create a line chart with the period on the x-axis and one line per type of expense. How can I create a measure that counts the number of people in each period, which can be filtered by attributes from the People table, but is not affected by the attributes of the Expenses table? If I just use Count(DISTINCT id_people), it only counts people who have an expense of the selected type. But if I use Count(TOTAL DISTINCT id_people), it ignores the period filter as well.
1. If the field id_people is a key field, I'd discourage you from using it for counting - you may get ambiguous results. Use a field that is not a key field.
2. If you just want to ignore the expense type as a dimension, and count the number of people for each period, you can still do it with the total qualifier:
Count(TOTAL <Period> DISTINCT id_people)
If you wanted to ignore any selections that might be made in the expenses table, then you'd need to add a Set Analysis condition and disregard selections from all related fields:
Count({<[Expense Type]=, Field2=, Field3=,...>} TOTAL <Period> DISTINCT id_people)
In addition, allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.
1. If the field id_people is a key field, I'd discourage you from using it for counting - you may get ambiguous results. Use a field that is not a key field.
2. If you just want to ignore the expense type as a dimension, and count the number of people for each period, you can still do it with the total qualifier:
Count(TOTAL <Period> DISTINCT id_people)
If you wanted to ignore any selections that might be made in the expenses table, then you'd need to add a Set Analysis condition and disregard selections from all related fields:
Count({<[Expense Type]=, Field2=, Field3=,...>} TOTAL <Period> DISTINCT id_people)
In addition, allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.
