Date Issue ( Do let me know if anyone knows the answer)

[#1]

Hi I new to Qlikview. I have to generate a report to find out number of documents created per moth.

One table has Document_ID and another table has Created_Date in the format (01-April-2001). I want to find out number of documents Created per moth or Number of documents created per year. I am attaching my table format for review.

Thans for your help.

Regards,

Vikas

[#2 · Accepted solution]

Hi,

try this->

Load Document_ID,

         Created_Date,

         year(Created_Date) as Year,

month(Created_Date) as Month

From Table;

then put this expression count(Document_ID) in the report and the Dimention should be Year and Month.

Regards,

Apurva

[#3]

Hi,

I think Apurva's example must be extended a bit, because this won't work (because I think the date-format won't be recognized immediately)

year(Created_Date) as Year,

month(Created_Date) as Month

I would suggest this load script

LOAD 
//see Qlikview reference manual EN page 866 for conversion of datetime
  timestamp#(CREATED_DATE,'DD-MMM-YY hh.mm.ss.fffffffff tt') as myConvertedDatetime,
//get the year dimension (using the same conversion expression as above)
year(timestamp#(CREATED_DATE,'DD-MMM-YY hh.mm.ss.fffffffff tt')) as createdYear,
month(timestamp#(CREATED_DATE,'DD-MMM-YY hh.mm.ss.fffffffff tt')) as createdMonth,
Document_ID, 
     CREATED_DATE
FROM
qvTestConvertDateTime\book1.xlsx
(ooxml, embedded labels);

See the attached zip-file for a working qvw and xlsx.

[#4]

Hi Apurva/Roberto,

Thanks for your help. Both of you are correct and my query is working fine.

Thanks for help and support.

Regards,

Vikas