YTD

[NavinReddy]

Dear All,

I am calculating ytd but im not getting any error in expression,but not working could some one help me

to do this

=sum({<Month={'$(vYearStart)'}>}Actuals) ------>This is working

=sum({<Month={'>=$(vYearStart)<=$(vCurrentMonth)'}>}Actuals) ----------->ITs Not working YTD

Please Help some one

Thanks In Advance

Niranjan

[Anonymous · Accepted solution]

Hi Niranjan,

Create one tab at last and add below Coding...

YTD Exression:

=Num(Sum({$<MonthID = {"<=$(=Max(MonthID))"},Year= {$(=Max(Year))},Quarter = ,Month = >} Actuals),0.00)

Budgets:

Load

     //Month,

    Makedate(2014,NUM(MONTH(vCurrentMonth)),1) as Date,

     Budget Resident Budget;

// Drop Butget Sales

DROP table Budget;

Master Calender script:

MinMax:

LOAD

  MIN(Date) AS MinDate,

  MAX(Date) AS MaxDate

RESIDENT Budgets;

LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));

LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));

LET vToday = $(vMaxDate);

//*****Temp Calendar*****

TempCal:

LOAD

  DATE($(vMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*****Master Calendar*****

MasterCalendar:

LOAD

TempDate AS InvoiceDate,

WEEK(TempDate) AS Week,

YEAR(TempDate) AS Year,

MONTH(TempDate) AS CMonth,

DAY(TempDate) AS Day,

WEEKDAY(TempDate) AS WeekDay,

AutoNumber(YEAR(TempDate) & MONTH(TempDate), 'MonthID') as [MonthID],

'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2)))  AS Quarter,

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,

AutoNumber(date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))),'QuarterID') as QuarterID,

DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

date(yearstart(TempDate,1,8),'YYYY') AS YearFiscal,

//INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,$(vToday),-1,1) * -1 AS LastYTDFlag

INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

INYEARTODATE(TempDate,$(vToday),-1,8) * -1 AS LastYTDFlag

//INYEARTODATE(TempDate,TempDate,0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,TempDate,-1,8) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[NavinReddy]

Hi Raju,

Im very dont know im getting same error, i tried the same way as you said in previous reply

I am attached sample application above please if you do it in the application

its better for me to understand ,dot know where its giveing error

thanks again

BestRegards,

Niranjan

[javier_florian]

Hi Niru,

Try with the follow expression:

sum({<Month={"<=$(=Num(Max(Month)))"}, Año={$(vMaxYear)} Actuals)

With vMaxYear-> =Max(Year(Your_Date))

-JFlorian

[Anonymous]

Hi Niranjan,

I couldn't debug this script from my machine even though i have written the script.

Please find the same.

[NavinReddy]

Hi Javier_florian,

sorry its not working ,i m attached sample app

any modification plase

Best regards,

Niranjan

[NavinReddy]

Hi Raju,

dont Know where i am done wrong ,i am atteching the image

please find the image and help me i stragguling lot

current month only i am getting correct values and

YTD and Full year i getting wrong values

Best regards,

Niranjan

[NavinReddy]

Hi raju,

When i am selecting the Net cash check,

YTD and Full Year  Budget values are same

how can i filter the values ,your help will really greate

Best Regards,

Niranjan

[Anonymous]

Hi Niranjan,

Add date filed in Sales and forecast table also and add this date filed even in linked table. so that all the table will have date reference. This will sort out your issues.

I would always suggest to create star schema data model, if you could create star schema you might avoid these complications.

Rgds,

Raju Kuppusamy

[NavinReddy]

Dear All ,

Finally its working i followed Raju technique

i created master calendar i linked with that to my fact table

then its working fine

thank you guys efforts

Best Regards,

Niranjan

[NavinReddy]

Hi Raju,

Thanks you somuch finally your suggession its helps lot to me

it was grate reply from your side thank you so much

Best Regards,

Niranjan