Match of Year of Sales table and Year of Target table

[giladmagori]

Hi,

I have a fact table of sales,

It has an OrderDate field which connect to Master Calendar table.

I have also a TargetCategory table as below;

u can see below the relevnt part of the data model viewer,

i made this screen, and my question is how choosing of year will choose also the correct year of target(same solution for Month)?

Thanks,

Gilad

[OmarBenSalem]

It would be sthing like this:

here's a script you can use to build a test application and understand how to handle different dates:

SET DateFormat='YYYY-M-D';

Table1:

LOAD * INLINE [

ID, Seq, ActivityDate, Value1

1, 1, 2011-5-10,10

1, 2, 2011-5-12,2

2, 1, 2011-5-12,20

3, 1, 2011-6-15,40

];

Table2:

LOAD recno() as ID, * INLINE [

DepartureDate, ArrivalDate, Value2

2011-6-15,2011-6-16,5

2011-6-15,2011-6-17,10

2011-6-16,2011-6-16,20

];

OtherTable:

LOAD recno() as OtherID, * INLINE [

OtherDate, OtherValue

2011-5-12,2

2011-6-15,3

2011-6-17,5

];

Link:

LOAD

ID

,Seq

,ActivityDate as Date

,'Activity' as DateType

RESIDENT Table1

;

CONCATENATE (Link)

LOAD

ID

,DepartureDate as Date

,'Departure' as DateType

RESIDENT Table2

;

CONCATENATE (Link)

LOAD

ID

,ArrivalDate as Date

,'Arrival' as DateType

RESIDENT Table2

;

CONCATENATE (Link)

LOAD

OtherID

,OtherDate as Date

,'Other' as DateType

RESIDENT OtherTable

;

Calendar:

LOAD *

,date(monthstart(Date),'MMM YYYY') as Month

;

LOAD date(makedate(2011,5,1)+recno()-1) as Date

AUTOGENERATE 61

;

credits to johnw