Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

3 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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