4 Replies Latest reply: Mar 5, 2014 1:15 PM by Ajay Krishnan Prabhakaran RSS

    Divide by number of days remaining in the week

    Jennie Elliott

      OK, I have a table for current and target gross revenue. Column 1, is current week gross revenue, Column 2 is Last Year Gross Revenue, column 3 is LY Gross Rev x 15%.


      My issue is with the Daily Needed Goal and Daily Stretch Goal.


      Work week is Sunday through Saturday. This table must show revenue through yesterday (column 1). The expression is column 2 minus column 1 divided by the number of days remaining in the work week. So it shows the correct 4 days remaining in the work week.


      The caption has the following expression in it:


           ='Current Week Revenue - ' & (Sum({$<CurrentPeriodWeek={1}, FutureDate={1} >}WorkDayCounter)+1) & ' Days Remaining'


      The Daily Needed Goal is using the following expression:


           (Column(2)-Column(1))/(Sum({$<CurrentPeriodWeek={1}, Date = {'<$(=Today())'}>}WorkDayCounter))


      In the calendar script, WorkDayCounter is written as follows:


           IF(WeekDay(Date)='Sun',0, Applymap('Holiday_Map',Num(Date), 1)) as WorkDayCounter,


      The Holiday Mapping script is:




      Num(HolidayCalendar.HolidayCalendar.Date) as HolidayCalendar.Date,    //Lookup Field to match on

      0 as HolidayCalendar.WorkDayCounter          //Value to return



      FROM [..\SourceDocuments\QVD\HolidayCalendar.qvd] (qvd);


      Bottom line; it is not working and sincerely need some assistance is getting this the calculate correctly. I appreciate any and all help.




      P.S. This was written by someone else and I am just trying to get it to work properly,