0 Replies Latest reply: Apr 6, 2017 10:03 AM by Preethi T

# Fiscal Year YTD calculation from given fiscal weeks using variables

Hi ,

I have the weekly data given for 4 years and need to calculate the YTD calculation for fiscal year starting from July.

For ex:

fiscalWeeknumbers  and CalendarWeekNumbers given like 201201,201202,201203............201252...........201352.......etc.

I have calculated a fiscal date using the calendarWeek numbers i.e. like this
date(weekend(MakeWeekDate(left(CalendarWeekNumber,4),right(CalendarWeekNumber,2))),'MM/DD/YYYY') as [FiscalWeekendDate]

Now, in order to calculate YTD calculation, I need the start and end date . So I created two variables like vEndDate= Date(Max([FiscalWeekendDate]),'MM/DD/YYYY')
and  vStartDate==Date(YearStart([FiscalWeekendDate],0,7),'MM/DD/YYYY')

Also the expression for YTD Sum from the start of selected year to the selected date is:

Sum({1<FiscalWeekendDate={'>=\$(=vStartDate) <=\$(=vEndDate)'}>}BillQty)
for ex: If 201510 is selected,then this should give me the sum of all the qty from 201501 week to 201510  th week.

But it gives me 0.

Also If I check the variables (vStartDate and vEnddate ),they render me 07/05/2014 and 09/06/2014 respectively.

(I used variables many times and as of I know we need to use variables as \$(vStartDate) but here if I use so it gives me some 0.003456 value and if I use '\$(vStartDate)' or only vStartDate, then it gives 07/05/2014) - Is this a usual behavior?

Can anyone check this and tell me where it goes wrong or any better way to do it?

Thank you!