Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewwizard
Master II
Master II

Date format Issue

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:

   

YearMonthActual_YearMonthBudget_YearMonth
2014-012015-012015-01
2014-012015-022015-02
2014-012015-032015-03
2014-012015-042015-04
2014-012015-052015-05
2014-012015-062015-06
2014-012015-072015-07
2014-012015-082015-08
1 Solution

Accepted Solutions
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

16 Replies
abchandru
Contributor III
Contributor III

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

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

Gabriel
Partner
Partner

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
Creator II
Creator II

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
Master II
Master II
Author

Hi Chandrasekar,

It is not working.

abchandru
Contributor III
Contributor III

Date format.GIF

qlikviewwizard
Master II
Master II
Author

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
Master II
Master II
Author

Hi Avinash,

Please check the below screens:

Capture.PNG

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,

Capture1.PNG

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

Great dreamer's dreams never fulfilled, they are always transcended.