Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Referencing a column of a dimension in Set Analysis

Hi,

I have a general question about set analysis in a star schema.

We all know that in start schema we have a fact table linked to many dimension via dimension key. Those dimensions and the fact table have another column.

Suppose that you want to sum a measure according a condition on a column dimension knowing that the column on which you would like to apply the condition is not the key.

For example,

Customer Dimension :

  • customer key
  • customer name
  • customer town
  • customer country

Date Dimension :

  • date
  • week
  • month
  • year

Sale Fact

  • date
  • customer key
  • sale

Which set analysis statement do you write to get the sum of sale of all customers of a specific country ?

Thanks in advance.

3 Replies
Anonymous
Not applicable
Author

Hello.

It doesn't matter if the field used in set analysis and the measure are on the same table. As long as there is a some link between their tables it will work.

In your case, the expression is:

Sum({$<[customer country] = {'Brazil'}>} sale)

Cheers.

Not applicable
Author

Thanks for answering but I'm not sure.

Let's imagine that there are many date in the Date Dimension, date from 2013/01/01 to 2020/12/31 and you have sales from 2013/04/01 to today. How do you write your SA statement knowing if your would like to take the max date in account ?

I think that we should handle link in that case.

Anonymous
Not applicable
Author

That would depend of your exact requirements. Please, see my answer in your other thread: Sum data if a month .

Another way to do it, since you seem to want a solution modifying the script, would be to create a second that field exclusively in the Sales Fact.

[Sale Fact]:

Load

     date,

     date as SalesDate,

     customer key,

     sale

From ...

    

Then, you could use the new field SalesDate (which will have sales only til today) in the set analysis.

Regards