Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
By applying below expression its is showing the some calculated value but Year is not getting selected directly
=Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date), 0, 8), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(Max(Date)), 'DD-MM-YYYY'))"}>} [Net Bookings])
I want to modify the expression suppose If I select the Year 2015 then it should pick up the value from Aug-2015 not from Aug-2014.
Pls check my Script below
Cisco:
LOAD Distinct
[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 FiscalYear,
//num(month(date#([Fiscal Period ID],'YYYYMM'))) as FiscalMonthNum,
// month(date#([Fiscal Period ID],'YYYYMM')) as FiscalMonth,
Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY') as Date,
//floor(Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY')) as DateNum,
Week(Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY')) as Week,
FMV,
[Net Bookings],
Group
FROM
[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Nikhil.G\CISCO\Copy of June raw.xlsx]
(ooxml, embedded labels, table is Sheet1);
//
//Left Join
//LOAD * Inline [
//FiscalMonthNum,CalenderMonth, CaldenderMonthNum
//1,Aug,8
//2,Sep,9
//3,OCt,10
//4,Nov,11
//5,Dec,12
//6,Jan,1
//7,Feb,2
//8,Mar,3
//9,Apr,4
//10,May,5
//11,Jun,6
//12,Jul,7 ];
////
//Final:
//LOAD *,
//monthname(Date#(CalenderYear&CalenderMonth,'YYYYMMM')) as CalenderMonthYear,
//date(Date#(CalenderYear&CalenderMonth,'YYYYMMM'),'DD-MM-YYYY') as CalenderDate;
//LOAD
//FiscalYear,
//FiscalMonthNum,
//CalenderMonth,
//[Fiscal Period ID],
//if(CaldenderMonthNum>=8,FiscalYear-1,FiscalYear) as CalenderYear
//Resident Cisco;
//////
//DROP Table Final;
//QuartersMap:
//MAPPING LOAD
//rowno() as Month,
//'Q' & Ceil (rowno()/3) as Quarter
//AUTOGENERATE (12);
//Temp:
//Load
// min(FisDate) as minDate,
// max(FisDate) as maxDate
//Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
//Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
//DROP Table Temp;
//TempCalendar:
// $(varMinDate) + Iterno()-1 As Num,
// Date($(varMinDate) + IterNo() - 1) as TempDate
// AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
//MasterCalendar:
// TempDate AS Date,
// week(TempDate) As Week,
// Year(TempDate) As Year,
// Month(TempDate) As Month,
// Day(TempDate) As Day,
// YeartoDate(TempDate)*-1 as CYTD,
// YeartoDate(TempDate,-1)*-1 as LYTD,
// inyear(TempDate, Monthstart($(varMaxDate)),-1) as Rolling12,
// date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
// ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
// Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
// WeekDay(TempDate) as WeekDay
//Resident TempCalendar
//Order By TempDate ASC;
//Drop Table TempCalendar;
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
Year(Date) AS Year, // Standard Calendar Year
Month(Date) AS Month,
Week(Date) as Week, // 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
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
I have tried every attempt but all in vain.
I want to calculate YTD,MTD and QTD on same sheet.My Fiscal Yeatr Starts from August
Thanks in Advance
Regards,
KK
