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.

      Targets.png

      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:

       

      Holiday_Map:

      MAPPING LOAD DISTINCT

      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.

       

      Jennie

       

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