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: 
amit_saini
Master III
Master 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
Master III
Master III

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

View solution in original post

15 Replies
Anonymous
Not applicable

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

qlikmsg4u
Specialist
Specialist

Hi Amit,

Like this in Attached?

Untitled.png

amit_saini
Master III
Master III
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable

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

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

Kushal_Chawda

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

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
Specialist III
Specialist III

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.