qliksense

QlikSense - retrieving row values outside of the current filter selection


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!


Solution

  • 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.