Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jenmclean
New Contributor II

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.

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,

1 Solution

Accepted Solutions
Not applicable

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.

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)

4 Replies
Not applicable

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?

jenmclean
New Contributor II

Re: Divide by number of days remaining in the week

Yes, you are correct

jenmclean
New Contributor II

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?

Not applicable

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.

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)

Community Browser