I am trying to use the following code but its only showing 2 weeks worth.
[Work Entry Week] between
_add_days(
(_add_days(
current_date,
-(_day_of_week(current_date,1) + 6))
), -7
)
and
(_add_days
(
_add_days
(
?pEndDate?,
-(_day_of_week(?pEndDate?,1))
), (_days_between (current_date, ?pEndDate?))
)
)
I'm not sure I understand what you're trying to get. Could you give us some examples / scenarios to see if that helps. It looks like your code has just two variables: current data and end date. Could you provide some examples of those two and let us know what data range you'd expect it to produce?
I have a report that shows number of hours spent by a category of job, example, engineers, Program manager, etc. I want to show a report that has hrs from last week till the end date that user picks in the prompt page.
Code above also says that Monday is day 1. Timesheet week starts from Monday till Sunday. So, when user pics End Date prompt, cognos knows to pick the week with Monday as day 1.
Please let me know if this helps.
To be concise: You want to report on data from Monday of last week through the date the user selects.
Is that correct?
You may have overcomplicated your expression. Try this:
_add_days(current_date, -_day_of_week(current_date,1)-7)
Thanks dougp. I need to put ?pEndDate? on the code too.
The title of the post (days between 7 days ago and a date prompt) must not be accurate.
By "7 days ago" you seem to want 7 days before the previous Monday.
What is is you really want for "date prompt"?
date prompt is the end date that that user picks. So, the data would be from 7 days before the current date till the end date that the user picks.
Hmmm... Not sure I understand what makes this so difficult. What's wrong with this?
[Work Entry Week] between _add_days(current_date, -_day_of_week(current_date,1)-7) and ?pEndDate?
I'm pretty sure what doug posted as the latest solution hits all your key notes. I could be wrong though.