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!
