Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

How to Count the week number of the particular date

Hi All,

I want to count the week numbers when the defects were opened and closed.

Attaching the excel sheet with the date, open week and close week of the defects.

For example,  as 6/1/2015 and 9/1/2015 falls under 2nd week

so I need to get the output as

DATE   Count of open   Count of close

5/1/2015      2                       1

In the output, 5/1/2015 is the weekstart of 6/1/2015 and 9/1/2015

similarly, the complete output

DATE   Count of open   Count of close

5/1/2015        2                       1

12/1/2015      1                       2

26/1/2015       1                      0

2/2/2015         1                      1

16/2/2015        1                     1

23/2/2015        1                     2

Could you help me in getting this output.

Thank you in advance.

1 Solution

Accepted Solutions
HirisH_V7
Honored Contributor

Re: How to Count the week number of the particular date

Hi ,

check this,

Table:

LOAD

Week(Date) as Week,

WeekStart(Date) as WeekStart,

WeekEnd(Date) as WeekEnd,

WeekDay(Date) as WeekDay,

Date AS ActualDate,

1 as Count,

* INLINE [

    Date

    5/1/2015

   10/1/2015

    14/1/2015

    15/1/2015

    26/1/2015

    29/1/2015

    3/2/2015

    7/2/2015

    11/2/2015

];

Hope this helps,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

6 Replies
engishfaque
Valued Contributor III

Re: How to Count the week number of the particular date

Dear Guddheti,

Go to "Edit Script" and write down listed below function with appropriate field names,

Week(CaseOpen_DateFieldName) as CaseOpen_WeekNo,

Week(CaseClose_DateFieldName) as CaseClose_WeekNo,

Kind regards,

Ishfaque Ahmed

Re: How to Count the week number of the particular date

Hi,

You can try the WeekStart Function

Check the Attachment. May be Helps

HirisH_V7
Honored Contributor

Re: How to Count the week number of the particular date

Hi ,

check this,

Table:

LOAD

Week(Date) as Week,

WeekStart(Date) as WeekStart,

WeekEnd(Date) as WeekEnd,

WeekDay(Date) as WeekDay,

Date AS ActualDate,

1 as Count,

* INLINE [

    Date

    5/1/2015

   10/1/2015

    14/1/2015

    15/1/2015

    26/1/2015

    29/1/2015

    3/2/2015

    7/2/2015

    11/2/2015

];

Hope this helps,

PFA,

Hirish

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

Not applicable

Re: How to Count the week number of the particular date

could you please tell how to count the week numbers

Not applicable

Re: How to Count the week number of the particular date

Hi Perisamy,

As 16/2/2015 is of 9th week. I want count of open to be 1 and close to be 0.(not 1 as below)

Thank you

HirisH_V7
Honored Contributor

Re: How to Count the week number of the particular date

Hi,

May be like this,

Count(Distinct Week)

will give you how many week happened for your Transaction or requirement according to your Date Field.

check the app modified in above post of mine

-Hirish

HirisH
“Aspire to Inspire before we Expire!”