Divide by number of days remaining in the week

[jenmclean]

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:

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,

[#2]

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?

[jenmclean]

Yes, you are correct

[jenmclean]

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?

[#5 · Accepted solution]

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

Please go to Settings > Variable Overview > Add

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)