Datamodel

[NavinReddy]

Dear All,

I created Master Calendar

when i am connecting with this one excel data working fine

In excel i have only two months data ,highlighted only two months ,Here im getting only 12 Months

When i am connection with same location one more excel data,

it has Jan ,Feb ,Mar Data but its showing Jan,Feb,Mar including 12months data(Jan,Feb,Mar+12Months)

Please help me some one

Thanks In Advance

Niranjan

[MK_QSL]

May be like this

Date(Date#(month,'MMM'),'MMM') as month

[its_anandrjs]

Sorry i miss some thing

Write like

=Month(Date(Date#(month,'MMM'),'MMM'))

it will convert JAN,FEB,MAR TO Jan,Feb,Mar

[NavinReddy]

Hi ,

i am atteching sample qvw please find the attechment

and here Month2 one excel sheet, Month one more excel,month1 master calendar

please help me

Regards,

niru

[its_anandrjs · Accepted solution]

If you not have common date field in the tables then use makedate command in the tables and then link it with the date field of the master calender

MakeDate(Year(Trim(Left([Pay Yr & Month],4))),month(date#(Trim(Right([Pay Yr & Month],2)), 'MM'))) as CreatedDate

in Occupancy table your Year  field is not proper as year it is in text format please check this and then use makedate function for creating date field for connecting with the master calendar.

Regards

[MK_QSL]

Your month field is coming from Pay Yr & Month which contains all Months from 2013 and Jan & Feb from 2014.

What exact problem you are facing. Still unclear from your question.

If the problem is that you are getting all months for year 2014... you can overcome the problem by joining your Fact Table with Master Calendar something like below..

Inner Join (OverTime) Load * Resident MasterCalendar;

Drop Table MasterCalendar;

Considering that Month is the common field here.

Another solution is to find out Min and Max Month from your OverTime table and to generate data for master calendar.

Hope this helps...

[NavinReddy]

Thanks you guys your reply is very help full

Thank you so much