Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
bruno_montenegr
Contributor III

Re: Referencing a column of a dimension in Set Analysis

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

Re: Referencing a column of a dimension in Set Analysis

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.

Highlighted
bruno_montenegr
Contributor III

Re: Referencing a column of a dimension in Set Analysis

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

Community Browser