Date format Issue

[qlikviewwizard]

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

[Anonymous]

you can convert INVDate format to ActaualYearMonth,BudgetYearMonth format using this

date(date#(INVDate,'YYYYMMDD'),'YYYY-MM')

[Gabriel]

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

[tripatirao]

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

[qlikviewwizard]

Hi Chandrasekar,

It is not working.

[Anonymous]

[qlikviewwizard]

Hi All,

Henric Cronström ,Avinash R ,Sasidhar Parupudi,swuehl

Any please help me.

[avinashelite]

Your Sales table is not connected with other tables ......create a link for the same

[qlikviewwizard]

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.

[PrashantSangle]

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