15 months graph

[#1]

I have a requirement of showing 15 months of for CY and LY.Currnetly i have a graph which does for 12 months as shown below.My master calendar has a cannonical date for Hire date and Term date and the fical month is being derived using this date.

My master calendar is :

SET vFM = 10;

SET vFiscalYearStartMonth = 10;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Commondate), 'MMM')), FiscalMonth) AS FiscalMonthName, // Fiscal Calendar Month Name

Dual(Text(Date(MonthEnd(Commondate), 'MMM')), FiscalMonth)&'-'& YearName(Commondate, 0, $(vFiscalYearStartMonth)) AS FiscalMonthName_Year; // Fiscal Calendar Month Name

LOAD

*,

Mod(Month(Commondate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Commondate, 0, $(vFiscalYearStartMonth)) AS FiscalYear,  // Fiscal Calendar Year

If(QuarterStart(Commondate,0,$(vFM))=QuarterStart(Today(),0,$(vFM)),1,0) as IsCurrentQuarter,

If(YearStart(Commondate,0,$(vFM))=YearStart(Today(),0,$(vFM)),1,0) as IsCurrentYear,

If(YearStart(Commondate,0,$(vFM))=Date(YearStart(Today(),0,$(vFM))-365),1,0) as IsLastYear;

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Commondate,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

and my two measures are  :

All Hires CY: Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(YearStart(Today(),0,10)))<$(=Num(Today()))"}

,DataType = {'Hire'}>} distinct [EmpUID])

All Hires LY :

Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(YearStart(Today()-365,0,10)))<$(=Num(Yearend(Today()-365,0,10)))"}

,DataType={'Hire'}>} distinct [EmpUID]).

Can you please help me on how to generate a grpah with 15 months showing for CY and LY.

[jagan · Accepted solution]

Hi,

Try this for last 15 Months based on the Current Date

=Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(MonthStart(Today(), -15)))<$(=Num(Today()))"}, DataType = {'Hire'}>} distinct [EmpUID])

Based on the selected date

=Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(MonthStart(Max(DateField), -15)))<$(=Num(Max(DateField)))"}, DataType = {'Hire'}>} distinct [EmpUID])

Hope this helps you.

Regards,

Jagan.