QlikSense - retrieving row values outside of the current filter selection

Question

I'm trying to create an expression that will retrieve values outside of the user's selection, and at a certain level. My fact table is stacked with multiple fact tables, an example of the data model below:

Tablename	Country	State	Sales	Target
Sales	US	California	100	null
Sales	US	California	200	null
Sales	US	New York	200	null
Target	US	null	null	500
Sales	CA	Toronto	100	null
Sales	CA	Toronto	200	null
Sales	CA	Vancouver	200	null
Target	CA	null	null	500

Sales are at a state/city level, whereas targets are at Country level. The users will select 1 state/city and put it against the country's target to see its contribution. For California, it will be 300 / 500 = 60%.

However since the users will filter by state/city, any target values will return 0.

I'm assuming p() needs to be used somewhere (since the relationship of sales and target are by the country value and not by association) but how do I achieve this via set analysis?

Also, what is the proper term used here where the Sales and Target rows have a relationship due to a common column value?

Thanks!

Answer 1

I was able to get this working with set analysis, the Aggr() function, and the total keyword. This expression works for me:

=Sum(Sales) / Sum(total <[Country]> Aggr(Sum({1} [Target]), [Country]))

The denominator in that expression broken out:

Sum(total <[Country]> Aggr(Sum({1} [Target]), [Country]))
      ^        ^       ^        ^
      3.       4.      2.       1.

1. Here we are summing up the [Target] field. We want to ignore users' selections for this part of the expression so we use the set identifier {1}.
2. We put that Sum() function inside an Aggr() function so that we can aggregate or group the Sum() on the [Country] field.
3. Since the Aggr() gave us a summed target value for each country, we now want to Sum() those values but give the total amount across all dimensions.
4. We can specify a dimension for total to ignore, in this case we chose the [Country] field so that we can see summed values rolled up to Country but not State/City.