Group By date issue

[syncdba]

Hi,

I'm trying to create a bucket by DATE in the following way in CRXI:

Today: 0 and -1 (or <= 0)
1 day: 1
2 day: 2
3 day: 3
4 day: 4
5 day: 5
6 day: 6
2 weeks: 7-13
3 weeks: 14-20
4 weeks: 21-27
1 month or more: 28+ (>= 28)
Never: null

The APPS DATE column shows data as
N/W -1
TCP 0
MULTIMEDIA 0
N/W -1

Thanks in advance..

 

[lbass]

Dates don't show numbers like -1 and 0. Are you trying to compare the date to the current date or some other date? If comparing to the current date, you could use a formula like:

if isnull({table.date}) then "Never" else
if datediff("d",{table.date}, currentdate) <= 0 then
"<= 0 Days" else
if datediff("d",{table.date}, currentdate) =1 then
"1 Day" else
if datediff("d",{table.date}, currentdate) =2 then
"2 Days" else //etc. add in other days else
if datediff("d",{table.date}, currentdate) < 14 then
"Two Weeks" else
if datediff("d",{table.date}, currentdate) < 21 then "Three Weeks" else
//etc.

-LB