Hi team

Karim_Khan — Mon, 10 Aug 2015 16:14:41 GMT

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

//Resident Cisco;

//Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

//Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

//DROP Table Temp;

//TempCalendar:

//LOAD

// $(varMinDate) + Iterno()-1 As Num,

// Date($(varMinDate) + IterNo() - 1) as TempDate

// AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

//MasterCalendar:

//Load

// 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

LOAD

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

LOAD

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,

topic Hi team in QlikView

Hi team