If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

days between 7 days ago and a date prompt

Started by Universe, 27 Jun 2019 04:12:15 PM

Previous topic - Next topic

Universe

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?))
)
)

BigChris

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?

Universe

#2
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.

dougp

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)


Universe

Thanks dougp. I need to put ?pEndDate? on the code too.

dougp

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"?

Universe

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.

dougp

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?

Arruyou

I'm pretty sure what doug posted as the latest solution hits all your key notes. I could be wrong though.