Referencing a column of a dimension in Set Analysis

[#1]

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.

[Anonymous]

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.

[#3]

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]

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