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

    Fiscal Year YTD calculation from given fiscal weeks using variables

    Preethi T

      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!