How to specify a specific relationship for calculations in Qlik Sense?

[Nileshsonkusare36]

Hello Qlik Community,

I am working on a Qlik Sense app with multiple tables and relationships. I have a scenario where I need to perform calculations using a specific relationship, rather than relying on the active relationship.

In Power BI, this can be achieved using the USERELATIONSHIP function. However, I'm not sure what the equivalent approach is in Qlik Sense.

Here's my scenario:
- I have two tables, 'Test Details' and 'DimDate'.
- There's an active relationship between 'Test Details'[First Test Date] and 'DimDate'[Date].
- I want to calculate the total scores based on both the first test date and the second test date, but I need to specify the relationship explicitly for the second date.

Can anyone please guide me on how to achieve this in Qlik Sense? I'm open to any alternative methods or functions that can help me achieve this goal.

Any assistance or examples would be highly appreciated. Thank you in advance!

[Mike_Dickson]

Hello @Nileshsonkusare36 

In Qlik Sense, instead of relying on active relationships like you would in a traditional database or even some other BI tools, Qlik Sense uses a concept of associative data model. This means all tables are linked based on common field names, and you can use this to your advantage.

Given your scenario, to perform calculations based on specific relationships, you can use a combination of Set Analysis and Rename Fields to ensure that you're working with the correct fields.

Here's how you can approach your scenario:

1. Rename the Field: To avoid ambiguous relationships, you can rename the field in one of the tables during the load script. For example, you can rename the 'Second Test Date' to 'SecondTestDate'.

   Test Details:
   LOAD 
   ...,
   [Second Test Date] as SecondTestDate,
   ...
   FROM [your_datasource];
   
   

2. Set Analysis: When performing calculations, use set analysis to specify which relationship you want to consider.

   • For First Test Date (using the default relationship):

     Sum({< Date = First Test Date >} Score)
     

   • For Second Test Date (using the renamed field):

     Sum({< Date = SecondTestDate >} Score)
     

Remember, in Qlik Sense, you're not setting a permanent relationship in the way you do in Power BI. Instead, you're defining associations through field names and then specifying, at the time of calculation, how you want to make use of those associations using set analysis.

Always ensure that you don't have synthetic keys (multiple fields in common between tables without resolving them) or circular references in your data model as this can lead to ambiguous results.

Lastly, be mindful of performance. If you have very large datasets, complex set analysis can introduce overhead. Always test with representative data and monitor the performance of your app.

[Zorak]

Hi @Mike_Dickson , I have a related problem. I have two tables, claims and policies, claims and policies are connected by policy number, but both of them have also a date column of claims notice and emission date. For some calculations I need them to be filter by policy number but for others for the date. I tried creating a master date table, but is not working.

Thanks for any help.

[DorianS]

Hi! In Qlik Sense, you can achieve the desired calculation using the 'Qualify' function to explicitly specify the relationship you want to use. Here's how you can do it: 

1. In your expression, use the 'Qualify' function to specify the table and field for the second date explicitly. For example:
   

   Sum({$<SecondTestDate = {'$(=Qualify('DimDate.SecondTestDate'))'}>} Score)
   

2. This expression will calculate the total scores based on the second test date using the specified relationship.

By using 'Qualify,' you can control which relationship is used for your calculations, similar to the USERELATIONSHIP function in Power BI.

For more detailed examples and guidance on working with Qlik Sense relationships, you can refer to the Qlik Sense documentation or explore resources like this QlikView for Salesforce review, which may provide additional insights and practical examples.