Set Anlysis after Linking tables

[Yakushi]

Hello, 

I don't know if this was asked before but :

I have two fact tables with different granularity and i linked them using Generic Keys.

Now i have data that looks like this (Straight table after displaying some fields from each table) :

No when i create a straight table with HeadID and calculate sum(AMOUNT), it gave me the good result, but if i filter with "cause" for example, the sum became 0, which is normal, but how to fix that ? Is there any way to get the right sum(amount) even when the selections are made ?

I tried this : 

=sum( {$< HEADID= P({1<DATA_TYPE={‘REPORT’}>} )>} AMOUNT)

but it doesn't seem to work.

Thank you so much ! 

[Yousef_Amarneh]

if you want to ignore the selection of "CAUSE" field you can use the below expression, so if the user selected any value from CAUSE field the number will not change. =sum( {$< CAUSE=>} )>} AMOUNT)

[Yakushi]

Youssef,

Thank you for you answer. It does work but there is a lot of fields the user can select on, is there any other more generic way ?

These Fields are coming from the fact table report ( see The DATA_TYPE  ), so in other terms i want to have the sum(Amount) for all my HeadID just like if it was made on the Sale (Fact Table). And I want to do it in chart and not in the script, so the user can filter with year/months of sales !

I must believe it's a common problem that occurs every time we deal with multiple fact tables right ?

Thx for your help

[Yousef_Amarneh]

Hi,

What I know, there are 2 ways to ignore the selection of a field(s), the expression that I shared with you, or using the below expression which will ignore all selections except Column1 and Column2

My advise for you is to use a better way to link the fact tables by merging them in one table using concatenate function, read about linktable, it will give you more ideas

=sum( {1<Column1=P(Column1),Column2=P(Column2)>} )>} AMOUNT)

[Yakushi]

I'm using generic Keys with link table to connect the two fact tables.

They don't have same granularity and they don't share any field..

For example, HEADID kan have several reports (let's say 3), and SALEID can have several sales (let's say 10), and one or multiple SALEID are attached to one HEADID. If i associate my data otherwise, i will have for each row in HEADID, all the records from the SALES fact table (3x10 rows) .

I can also concatenate them and use generic keys to associate the resulting table with dimensions, but i will end up with the same result that i have now (10 lines for sales + 3 for reports) 

do u think i'm wrong ?