Reporting Period Confusion!! 2

[Monkeyboy126]

Hi

I have a table with many fields but I am only interested in 2 (Date opened & date closed).

! !
! !
x-----!--o !
! !
! x--------o !
! x-----!-----o
! !
!<---this period only--->!
! !

So on the above diagram (if it displays correctly) x=open date and o=closed date. The period that i want to report on may be a week, month etc (as displayed above) and should include calls in an open state but opened before the period, calls opened and closed in the period and finally calls opened in the period but still open.

Any ideas, I'm really struggling with this one...

Regards


Paul

 

[johnstrang]

Hello Paul,

My intial thought is that you need a select formula similar to:

Date Opened <= End of Period AND
Date Closed >= Start of Period

That seems to cover all 3 of the examples you posted, so I hope this is of help to you.

Good Luck!

John

 

[Monkeyboy126]

Thanks John

I think your right, however the selected date range is a parameter field (user chooses). How would I separate the date range selected in the parameter field to match your suggestion?

It would need to know the start and end date selected, wouldn't it??

 

[tdong]

Yes you definitly need the start and end date or the start and end selection that you are able to do the comparison and narrow the selection. I know how to do this in selection expert but I don't know how to prompt the user for input heheh I was posting a similar question too.

 

[Reebo99]

If the parameter is a range then use :

{DateOpened} in {?DateRange} OR
{DateClosed} in {?DateRange}

Replace the items in bold with your real fields and the items in italics with your parameter field.

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Monkeyboy126]

Thanks guys

Ok, half way there...

This is the selection formula in use:

{int_org.iorg_name} = "Company A" AND
{@Open Date} in {?DateRange} OR {@Close Date} in {?DateRange}

The date range now works but the formula seems to ignore the first part (the company bit) whereas this worked before.

Any last ideas?


Many thanks


Paul

 

[Reebo99]

Use:

{int_org.iorg_name} = "Company A" AND
(
{@Open Date} in {?DateRange} OR
{@Close Date} in {?DateRange}
)


Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[johnstrang]

Paul,

My guess is that it's evaluating the formula wrongly, so I would suggest putting brackets round the "date" part of your formula:

{int_org.iorg_name} = "Company A" AND
( {@Open Date} in {?DateRange} OR {@Close Date} in {?DateRange} )

John

 

[Monkeyboy126]

The requirement has changed slightly and instead of a date range selected from a parameter field, the date range needs to automatically be the current month, so I know I am warm with the following formula:

Date(Year(CurrentDate),Month(CurrentDate),1)

But the formula that I am using is as follows and produces an error:

{@Open Date} in (Date(Year(CurrentDate),Month(CurrentDate),1)) OR
{@Close Date} in (Date(Year(CurrentDate),Month(CurrentDate),1)))

Any ideas??

Thanks

 

[Reebo99]

Use:

({@Open Date} in MonthToDate OR
{@Close Date} in MonthToDate)


Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[Monkeyboy126]

Excellent, it works...

Thanks Reebo

 

[Monkeyboy126]

Oh, found an exclusion.

This doesn't seem to work if for the chosen period (current month) the call is not closed (open state) and was opened the previous month...

How would I include these calls??

H E L P ! ! !

 

[GMcNamara]

I think what you essentially need is:

(isNull(Table.Close_Date) or
Table.Close_Date >= {?Start_Date}) and
Table.Open_Date <= {?Stop_Date}

 

[Monkeyboy126]

Still not working. The formula below still shows any calls closed for any company (not just company x) and wont display any Open calls.

{int_org.iorg_name} = "Company x" AND
({@Close Date} in MonthToDate OR isNull({@Close Date}))

The formula below shows just company x but wont show calls that are still open but were logged BEFORE current month..

{int_org.iorg_name} = "Company x" AND ({@Open Date} in MonthToDate OR
{@Close Date} in MonthToDate OR ({chg.active_flag}=1))

I'm going grey and losing my hair...

Thanks

 

[lbass]

I think GMcNamara is correct. Here's a slight variation:

(
isNull({Table.Close_Date}) or
{Table.Close_Date} >= minimum(monthtodate)
) and
Table.Open_Date <= maximum(monthtodate) and
{int_org.iorg_name} = "Company x"

-LB

 

[GMcNamara]

ALWAYS perform the null check before you compare the value to another known value.