Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am getting date format issue as described below:
INVDate is in YYYYMMDD format, data is getting from the database.
ActaualYearMonth,BudgetYearMonth format is in YYYY-MM and data is getting from Xls files.
Unable to get the data due to date format different.
Please help me.
Data:
YearMonth | Actual_YearMonth | Budget_YearMonth |
2014-01 | 2015-01 | 2015-01 |
2014-01 | 2015-02 | 2015-02 |
2014-01 | 2015-03 | 2015-03 |
2014-01 | 2015-04 | 2015-04 |
2014-01 | 2015-05 | 2015-05 |
2014-01 | 2015-06 | 2015-06 |
2014-01 | 2015-07 | 2015-07 |
2014-01 | 2015-08 | 2015-08 |
Hi,
that is your issue. there date field converted into num.
so you have to convert it either %CntryProdMnthKey1 and %CntryProdMnthKey2 or %CntryProdMnthKey3
modify your key
%CntryProdMnthKey3 = country&product&Date(YearMonth,'YYYY-MM')
Then reload it and check.
Regards
you can convert INVDate format to ActaualYearMonth,BudgetYearMonth format using this
date(date#(INVDate,'YYYYMMDD'),'YYYY-MM')
Hi,
What I would suggest is to have a Calendar Dimension table. All your dates from other Dimension/Fact tables should associate with Dates,Month,Year, Year-Month in the Calendar dimension table
Hi
I think you can use makedate() function.
MakeDate(YYYY [ , MM [ , DD ] ] )
Returns a date calculated from the year YYYY, the month MM and the day DD.
If no month is stated, 1(January) is assumed.
If no day is stated, 1 (the 1:st) is assumed.
Examples:
makedate(1999) returns 1999-01-01
makedate(99) returns 0099-01-01
makedate(1992,12) returns 1992-12-01
makedate(1999,2,14) returns 1999-02-14
Hi Chandrasekar,
It is not working.
Your Sales table is not connected with other tables ......create a link for the same
Hi Avinash,
Please check the below screens:
I joined between 3 tables with concatenated key country,product,yearmonth columns.
OP: %CntryProdMnthKey = country&product&OP_YearMonth
Forecast: %CntryProdMnthKey = country&product&Forecast_YearMonth
OP: %CntryProdMnthKey = country&product&OP_YearMonth
But associate betwwn these 3 columns are not happens.
OP_YearMonth and Forecast_YearMonth are coming from xls file.
YearMonth is derived from Sales QVD:
date(date#(MakeDate(SalesYear,if (SalesMonth<='9', '0'&SalesMonth,SalesMonth),'01'),'M/D/YYYY'),'YYYY-MM') as YearMonth,
Please advise.
Hi,
can you post screen shot of %CntryProdMnthKey field after above selection.
also create same key with different name in each table and also post there screen shot.
Regards