QTD calculation issue

[Karim_Khan]

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

[jonathandienst]

'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.

[Karim_Khan]

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

[jonathandienst]

How have you defined the variable vMaxDate?

Did you try with a literal date?

You probably don't want that Num() in the expression.

[Karim_Khan]

I have used the vMaxDate in Expression variable to get the Maxdate