Problem while retrieving data linked to a Canonical date

[Anonymous]

Hi all,

I am having a problem while creating a canonical date. I don't know whether it is about the creation of canonical date itself or data modeling.Canonical Date

Below is the data Structure before creation of canonical date:

Now there is the data after the creation of canonical date:

For looking a the tables everything seems ok, so far.

Here is the script to date creation:

Datelink:

load

  [S/N],

  DATE_CASH      as Date,

  'CASH'     as DateType

Resident CASHFLOW;

load

  [S/N],

  Date_COST as Date,

  'Cost'   as DateType

Resident COST;

Call CalendarFromField('Date', 'CommonCalendar','');

Call CalendarFromField('Date_COST', 'CostCalendar','Cost');

Call CalendarFromField('DATE_CASH', 'RevenueCalendar','Revenue');

Now what I would like to do is to show values of CASHFLOW and US$ fields for a chosen date, which comes from common calendar, provided by datelink.However I am getting the sum of all the years replicated in a chart as below:

Dimension: YEAR

Expressions:

sum({$<DateType={'COST'}>}US$)

sum( {$<DateType={'CASH'}>}CASHFLOW )

Does anyone know what is the problem?

Thank you!

[swuehl]

Is S/N granular enough to represent the single Amounts of COST and CASH on these dates?

Compare to HIC's blog post, where the link between the DateLink table and the facts is OrderLineID.

[Anonymous]

Yes, it is all granular, as seen above.

[swuehl]

If you select a value in Date field, only the same value should be possible (white) in fields Date_COST / DATE_CASH.

I believe you will see a lot more values, because your S/N field is not granular enough to link the DateLink table with your fact tables. You would need to use something like a record ID as key field.

[Anonymous]

You were right. In the case above I selected the year of 2016 for DATE_CASH / Date_COST. Now, when I select the Year in Canonical Date it appears as below:

Do you have an Idea where I put a new ID key? COST table or CASHFLOW table?

[swuehl]

What is ID_CASH and ID_COST?

Have you considered concatenating your fact tables?

[Anonymous]

ID_CASH = 'ACTUAL' or 'PAST'

ID_COST = RowNo()

Not Yet, But I will try to do that.

[swuehl · Accepted solution]

Try concatenating your two fact tables and while concatenating, create a new field Date by renaming your cost resp. cash date field (you then don't need the DateLink table).

[Anonymous]

Thank you. It solved my problem.