Date Cound Help based on Year and Month selection

[amit_saini]

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

[amit_saini]

Sorry right script:

LOAD *,

  month(Date) as Month,

  Week(Date) as Week,

  year(Date) as Year;

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

[sasiparupudi1 · Accepted solution]

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

[jagan]

Hi,

Try this script

LOAD *,

  month(Date) as Month,

  Week(Date) as Week,

  year(Date) as Year;

LOAD

Date(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(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

Now in the front end try like this

Create variables :

vStartDate = MakeDate(Max(Year), Max(Month))

vEndDate  = MonthEnd(MakeDate(Max(Year), Max(Month)));

Now in expression use this:

Count({<DOH={'>=$(=vStartDate)<=$(=vEndDate)'}>} DOH)

Hope this helps you.

Regards,

Jagan.

[amit_saini]

Thanks Sasidhar

[jagan]

Hi Amit,

Always format the date fields in the backend itself, otherwise you have you to specify the format everywhere you are using the datefield.  It also causes performance issues.

Also always try to use Set Analysis in expressions for better performance.

Hope this helps you.

Regards,

Jagan.

[amit_saini]

Hi Jagan,

Thanks for your suggestion , will follow the same from next time.

Thanks,

AS