Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
Thanks Sasidhar
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.
Hi Jagan,
Thanks for your suggestion , will follow the same from next time.
Thanks,
AS