DATESales query subject Relationship with Month query subject

[matrixfree]

Hello again,

We have:

DATEtransaction query subject
and
Monthtable

We need to link them both in Framework Manager by using the month column of the Monthtable and retrieving the month for the sale date in our Datetransaction query subject.

How exactly I do this ? Do I need to create a new query item first that best my months from our date query item in order to then be able to link them both by month ?

Thanks to anyone for any feed-back !

[schrotty]

Hello,

How exactly I do this ? Do I need to create a new query item first that best my months from our date query item in order to then be able to link them both by month ?

yes,  this is a solution for your problem.
get the month from your sales date ( use the extract-function to get a datepart of the sale-date i.e extract(month, [sales date]) ).
and define the relationship.


Schrotty

[cognostechie]

Hello again,

We have:

DATEtransaction query subject
and
Monthtable

We need to link them both in Framework Manager by using the month column of the Monthtable and retrieving the month for the sale date in our Datetransaction query subject.

How exactly I do this ? Do I need to create a new query item first that best my months from our date query item in order to then be able to link them both by month ?

Thanks to anyone for any feed-back !

You should have a Date Dimension (table) instead of a Month table. The Date Dimension will be joined to the Transaction table with the Date column. The Month will be a column in the Date Dimension so you would be able to get the Month for the Date in Transaction Table.

[beinownow]

Schrotty,

In fact i also found out that i can still perform a substr on the date in order to get the number of the month.

EX :
extract(month,[date data item]) which gives the numeric value of a month.(example : 4 for April)

To then display it as a string ('April', for example), which is our real need, i could then use a Case Statement

case extract(month,[DateTransaction] )
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
else 'error'
end

QUESTION:
BUT I ALSO NEED TO BE ABLE TO DISPLAY EIGHTER THE ENGLISH NAME OF THE MONTH OR THE ITALIAN NAME OF THE MONTH.
Does someone know how to acheive that ? That way i don't need my month table anymore and therefore one less join to do between tables.

Thanks !

[cognostechie]

Shortcut ! Shortcut ! Shortcut !  Do it for now and forget about what happens later !  Way to go !

[bdbits]

The way to achieve that is to do what cognostechie said. You need to create a date dimension and link to it, then put whatever ways you might want to display that date in the date table - for example just the month name in multiple languages.

You will thank us later when you understand BI a little better.  8)