How to link values using set analysis ?

[#1]

Hi,

I've a set analysis (or modelling) problem :

If you have difficulties to understand my post 🙂 , please open the attached qvw (I think it's more simple to understand)

Suppose I've three tables :

Receipts, Contracts and Sinisters. That three tables are linked by two key fields : Id_Products and Id_Contracts.

BUT for in some table calculations, I have to link Receipts Sinisters on date value (I want to display total Receipts value and total Sinisters value on a single time
scale). I cannot directly link dates in the script/model because it will create a cycle (you won't see it in the attached file yet but in real life, it does :).

I'm fighting with Set Analysis to do this :

I've used the "Receipt date" in Dimension and I'm trying to write a expression which will give me the good total Sinister value such as

=sum({$<Year_Accident=Year_Receipt,Month_Accident=Month_Receipt>}Receipt_Value))

But It doesn't work

Do you have any idea ?

(please tell me if you don't understand my question)

Best regards

P.S : I'm using V8.5 SR3 but I also accept V9 SR3 solution.

[#2 · Accepted solution]

Hi,

Instead of linking the table. Concatenate 3 tables (Contracts, Receipts, Sinister) and try the Set expression.

Regards

[#3]

Hi,

In fact, even if I concatenate the 3 tables, the Set expression, does not completely works :

Even If I have the Year and Month dimensions displayed in my table. When I'm using Set analysis, QlikView consider all Year possible values and all month possible values instead of using year and month value displayed in the header of the table. So it only works if I select one month and one year.

Anyway I'm gonna try your concatenate solution by creating a common date field between Receipts and Sinister..

Regards

[#4]

Thanks for your help,

it works now