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

[its_anandrjs]

You have to use Datefield inplace of the Month field that will be better.

[NavinReddy]

Hi anand chouhan,

sorry dont know how to solve this i attached sample app

please check and any suggessions

Best Regards,

Niranjan

[Anonymous]

Try this expression.

=sum({<Month={">=$(vYearStart)<=$(vCurrentMonth)"}>}Actuals)

Regds.

Raju Kuppusamy

[NavinReddy]

Hi Raju,

No the same error again result i m getting

Thanks for reply

Best Regards,
Niranjan

[Anonymous]

Hi Niranjan,

Your variables are representing text values.

First expression is working case conditions are just like where condition but in second expression you have been using

<=,>= conditions. You can use this conditions in only numeric values.

Assign IDs for each month and apply this condition then it will work.

Regards,

Raju Kuppusamy

[NavinReddy]

Hi raju,

How can i do this ,

you mean to say converting into nummeric format

if you have any idea i attached sample app ,

please give me example its better to me understand

Best Regards,

Niranjan

[#8]

Hi,

PFA

use month number instead of Month,

Hope it ll help....

[NavinReddy]

Hi Saranya Arumugam,

Thanks for your reply

its giving full year values

its not filtering the YTD please check im not able to figure out this

Best Regards,

Niranjan

[Anonymous]

Hi Nirajan,

Use Makedate() function to create date field in fact table.  then use master calender to format your months, year, qtr .. etc..

Once you added then write set expression coding to calculate YTD values.

Ex YTD Expression:

=Num(Sum({$<MonthID = {"<=$(=Max(MonthID))"},YearFiscal = {$(=Max(YearFiscal))},Quarter = ,Month = >} #SalesMT),0.00)

Below is the master coding:

MinMax:

LOAD

  MIN(InvoiceDate) AS MinDate,

  MAX(InvoiceDate) AS MaxDate

RESIDENT Facts;

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 Month,

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;

Regards,

Raju Kuppusamy

[NavinReddy]

Hi Raju,

Thanks for your efforts,thank you so much spending your valuble time

But when i m loading that code im getting table not found,script error these errors i am getting

please provide if you done previous any QVW its better to under stand to me

The code giving 3 errors

Best Regards,

Niranjan