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

# Divide by number of days remaining in the week

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:

Holiday_Map:

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,

• ###### Re: Divide by number of days remaining in the week

Hey Jennie,

Just a suggestion. So the caption expression seems to be working fine right.

(Sum({\$<CurrentPeriodWeek={1}, FutureDate={1} >}WorkDayCounter)+1) =4  which would change everyday. Say today this is 4 so the required expression would be

(Column(2)-Column(1))/4

Tom this would be

(Sum({\$<CurrentPeriodWeek={1}, FutureDate={1} >}WorkDayCounter)+1) =3 and required expression :

(Column(2)-Column(1))/3

Is this correct?

• ###### Re: Divide by number of days remaining in the week

Yes, you are correct

• ###### Re: Divide by number of days remaining in the week

I think I figured it out... my expression is:

(Column(2)-Column(1))/Sum({\$<CurrentPeriodWeek={1}, FutureDate={1} >}Total WorkDayCounter)+1)

Does that make sense?

• ###### Re: Re: Divide by number of days remaining in the week

You would have to store this in a variable and then use it in the expression.

vDate

= Sum({\$<CurrentPeriodWeek={1}, FutureDate={1} >}Total WorkDayCounter)+1)

Note: The '=' sign is important

Now,

The expression would be

(Column(2)-Column(1))/\$(vDate)

To test vDate,

Please put it in a text box and see if its showing the right value.

=\$(vDate)