Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

amit_saini
Honored Contributor III

Date Cound Help based on Year and Month selection

Hi Folks ,

Below are my Year and month filter:

Requirement: I'm having a filed called DOH (which is having date inside it) , like below:

 

DOH

Oct, 09 2015

Feb, 11 2014

Sep, 08 2014

Oct, 14 2014

Apr, 26 2015

Aug, 30 2015

Feb, 02 2015

Apr, 25 2015

Mar, 06 2015

Aug, 25 2014

Aug, 25 2015

Jul, 11 2013

I want to calculate count of DOH based on Year and Month selection.

For example suppose selected Year = 2015 , Month = Apr

so i should get count output = 2 ( See the red text in DOH) ---> count should change based on selection of Year and Month

Kindly suggest what should be the expression  for this requirement .

Thanks,

AS

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Date Cound Help based on Year and Month selection

User year and Month as your dimension

and the following expression for the count

sum(if (Year(Date#(DOH,'MMM, DD YYYY'))=Year and Month(Date#(DOH,'MMM, DD YYYY'))=Month ,1,0))

HTH

15 Replies

Re: Date Cound Help based on Year and Month selection

Could you share the expression you do the Count() with ?

qlikmsg4u
Valued Contributor

Re: Date Cound Help based on Year and Month selection

Hi Amit,

Like this in Attached?

Untitled.png

amit_saini
Honored Contributor III

Re: Date Cound Help based on Year and Month selection

Bill,

It should be =count(DOH) , but inside I need to restrict data based on Month and Year selection.

Not sure what should be the condition inside expression for Month and Year

Thanks,

AS

MVP
MVP

Re: Date Cound Help based on Year and Month selection

Hi,

If you arrive Year and Month from that date field DOH and your DOH is linked to Year and Month then you can directly use

Count(DOH)

Is this you are expecting?  If not can you attach sample file.

Regards,

Jagan.

Re: Date Cound Help based on Year and Month selection

A list Box by default shows all values, colour coded :

  • Green     Selected
  • White     Included
  • Grey       Excluded

The power of Green White and Grey

If you use a Table object then it will only show what is selected.  See attached.

Not applicable

Re: Date Cound Help based on Year and Month selection

PFA the expected result. You can use makedate() in this case.

Re: Date Cound Help based on Year and Month selection

Create Month and Year from DOH.

Year(DOH) as Year,

Month(DOH) as Month

Create straight Table

Dimension = Year, Month

Expression = Count(DOH)

amit_saini
Honored Contributor III

Re: Date Cound Help based on Year and Month selection

Hi Jagan,

See below:

Here above count against DOH for June month should be near about 40 only and not 3203.

I think it is giving me count of entries against June in DOH , but I'm looking count when DOH is having any date entry for June Month

Script:

LOAD

Date#(Date, 'MMM DD, YYYY') as Date,

     Name,

     text(Revision) as Rev,

     [ECO state],

     [Change Description],

     [Due Status ECO],

     [ECO Running Time],

     [Due Date for Released Design],

Interval(Date#(Date, 'MMM DD, YYYY')-Date(Date#([Due Date for Released Design],'MMM, DD YYYY')),'d')  as NoOfDays,

     Date#([True Date for Released Design],'MMM, DD YYYY') as DOH,

     [Lead Program],

     [Cutomer Project Code],

     [Customer Brand] as CB,

     [Customer Group],

     [Product Family of Lead Program],

     [Additional Programs],

     Owner,

    left( [Location of Owner], 3) as Plant_Number,

     [Category of Change],

     Originated,

     Originator

FROM

Thanks,

AS

senpradip007
Valued Contributor III

Re: Date Cound Help based on Year and Month selection

Hi Amit,

As you write Date#(Date, 'MMM DD, YYYY') as Date in your script, please try with

Date#(Date, 'MMM, DD YYYY') as Date.

Hope it will help.

Community Browser