Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I am able to calculate YTD and MTD on Fiscal Calendar basis.
But my QTD is getting calculated on Indian Calendar basis.Pls check my below expression and suggest.
MTD,
=sum({<Year={2015}, Month = {"$(vCisco)"}>}[Net Bookings])
YTD,
=sum({<Year={2015},Month={">= $(vFiscalYearStartMonth) <= $(vMaxMonth)"},Month={'<=$(vFiscalNum)'}>}[Net Bookings])
QTD,
=num(sum({$<Year = {$(=only(Year))}, Quarter = {$(=only(Quarter))}, Month = {"<=$(=max(Month))"}>} [Net Bookings]))
Hi karim,
Do you have the Separate Field for FiscalQuarter like FiscalyearStartMonth, or Quarter field describe as Fiscal Quarter?
May be you can create the separate field for Fiscal Quarter, then you can use that in your QTD expression..
Hi Settu,
I have created the Fiscal Quarter in Calendar generation I don't have the separate field.Pls check my script and suggest
Cisco:
LOAD [End Customer Global Ultimate Name],
Country,
[End Customer HQ Top],
[Sales Level 1],
[Sales Level 2],
[Sales Level 3],
[Sales Level 4],
[Sales Level 5],
[Partner Name],
[Fiscal Period ID],
Year(Date#([Fiscal Period ID],'YYYYMM')) AS Year,
Month(Date#([Fiscal Period ID],'YYYYMM')) AS Month,
Week(Date#([Fiscal Period ID],'YYYYMM')) AS Week,
Date(Num(Date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY')) AS Date,
FMV,
[Net Bookings],
Group
FROM
\\india.eclerx.com\ctrxdata\ARLM_R_DATA\Karim.Khan\Desktop\Sample_File.xlsx
(ooxml, embedded labels, table is Sheet1);
SET vFiscalYearStartMonth = 8;
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(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD
*,
//Year(Date) AS Year, // Standard Calendar Year
//Month(Date) AS Month, // Standard Calendar Month
Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
Hi,
In the script, you have field FiscalQuarter. But, why did you use the Quarter Field in your QTD expression.
may be try this..
QTD
=num(sum({$<Year = {$(=only(Year))}, Quarter = {$(=only(FiscalQuarter))}, Month = {"<=$(=max(Month))"}>} [Net Bookings]))
Thanks a lot sir u solve my big hurdle sir Once again Tnx