Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Pls help
while am applying the thing on set expression for QTD calculation its not working properly.
Sum({<Quarter=, Month=, Quarter=, Week=, Date=,DateNum={'>=$(=Num(QuarterStart(vMaxDate), 0, 8))<=$(=vMaxDate)'}>} [Net Bookings])
Pls check my below script
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 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,
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,FiscalQuarter
1,Aug,8,Q1
2,Sep,9,Q1
3,Oct,10,Q1
4,Nov,11,Q2
5,Dec,12,Q2
6,Jan,1,Q2
7,Feb,2,Q3
8,Mar,3,Q3
9,Apr,4,Q3
10,May,5,Q4
11,Jun,6,Q4
12,Jul,7,Q4 ];
//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(Date) as minDate,
max(Date) 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;
Regards,
KK
'not working properly' is rather vague. What exactly is going wrong and what did you expect and what have you tried to resolve it?
How have you defined the variable vMaxDate?
Did you try with a literal date?
You probably don't want that Num() in the expression.
Hi,
I have tried to make the changes through
Sum({<Quarter=, Month=, Quarter=, Week=, Date=,DateNum={'>=$(=Num(QuarterStart(vMaxDate), 0, 8))<=$(=vMaxDate)'}>} [Net Bookings])
but calculatuion is showing worng. and If try with
=SUM({<Year={'$(VCurrentYear)'},Quarter={'$(vQuarter)'},Day={'<=$(vMaxDay)'},Date=,MonthYear=>}[Net Bookings])
then it gives me Same calculation as of Monthly calculation.
Pls suggest
How have you defined the variable vMaxDate?
Did you try with a literal date?
You probably don't want that Num() in the expression.
I have used the vMaxDate in Expression variable to get the Maxdate