crystal formula help needed asap

[bubu2006]

I have a formula which has following code


if {@number of days}<=120
then
'0-120'
else if {@number of days} >120 and{@number of days} <=150
then '121 - 150'
else if {@number of days} >150 and{@number of days} <=180
then '151 - 180'
else if {@number of days} >180 and{@number of days} <=210
then '181 - 210'
else if {@number of days} >210 and{@number of days} <=240
then '211 - 240'
else if {@number of days} >240 and{@number of days} <=270
then '241 - 270'
else if {@number of days} >270 and{@number of days} <=300
then '271 - 300'
else if {@number of days} >300 and{@number of days} <=330
then '301 - 330'
else if {@number of days} >330 and{@number of days} <=360
then '331 - 360'
else if {@number of days} >360 and{@number of days} <=390
then '361 - 390'
else if {@number of days} >390 and{@number of days} <=420
then '391 - 420'
.....
Can I write a small code so that I don't need to write too many if else statement? The code will increment the vale for each 30 days after 120 days.
ANy help will be appreciated.

I was trying to write something like
numbervar x:=0;
numbervar y:=120;
stringvar str;
numbervar i=0;
for i:=120 to 3651 do
if y<=120 then
str:=totext(x)+'to'+totext
else
x:=y+1;
y:=y+30;
str:=totext(x)+'to'+totext;
i:=i+30;
str

Not sure how to hold previous value, so my code did not work properly.
Thanks,
bubu

 

[fisheromacse]

This is off the top of my head and without crystal in front of me to test, but perhaps something like this:


numbervar btn := ({@number of days}-120)/30;
numbervar svn := (120+((btn-1)*30)+1;
numbervar evn := (120+(btn*30);


IF {@number of days} <= 120
then "0 to 120"
else
svn &" to " evn

 

[bubu2006]

I was getting same value with my code before I switch to if..else, Your code is returning values like 1001 to 1030,1002-1032.. and so on. Not going to 120,150 series.

 

[fisheromacse]

oops, i forgot to remove the decimals from the variable btn.
change that line to:

numbervar btn := TRUNCATE(({@number of days}-120)/30);

 

[bubu2006]

this is doing the math but not working properly. I am getting values from 1021 not from 121. So it is adding 1001 instead of 1 with 120.

 

[bubu2006]

I guess the sorting order is not working properly because I need to convert that as string

 

[lbass]

Try this:

numbervar days := {@numberofdays};
stringvar x := "";
numbervar i := 0;

for i := 4 to 100 do( //replace 100 with an upper limit
if days <= 120 then
x := "000 to 120" else
if days in (i*30)+1 to (i+1)*30 then
x := totext((i*30)+1,"000") + " to "+ totext((i+1)*30,"000")
);
x

-LB

 

[bubu2006]

Because we are converting days series from number to string the sorting is not perfect. I am attaching the file. The formula I wrote and the help I got from fisheromacse and labss all have same issue. I am creating a crosstab with that formula and putting that into my crosstab row. I have tried to create a sort on numeric formula but that did not help.
Following is the data

AN BR HA HO MI NO NY Total
0 to 120 6875 500 0 800 1250 5750 2700 17,875.00
1021 to 1050 33227 10578 3830 43419 0 50084 3875 145,013.00
1051 to 1080 24787 5917 4899 13985 0 20586 0 70,174.00
1081 to 1110 22552 4994 8341 5827 1598 60064 2250 105,626.00
1111 to 1140 27636 2400 12575 3998 250 59544 0 106,403.00
1141 to 1170 21371 0 1575 4256 375 29332 2132 59,041.00
1171 to 1200 20840 4250 1250 0 500 35659 529 63,028.00
1201 to 1230 54253 500 13155 500 4875 14257 1150 88,690.00
1231 to 1260 29998 400 7920 2024 0 4466 913 45,721.00
1261 to 1290 18516 750 7112 1929 0 500 2718 31,525.00
1291 to 1320 37522 5945 29484 3081 750 50 6224 83,056.00
1321 to 1350 23123 700 1407 4340 3525 1249 4850 39,194.00
1351 to 1380 20481 1330 3023 12519 650 1500 6125 45,628.00
1381 to 1410 4879 1035 9010 12814 100 4500 6625 38,963.00
1411 to 1440 6512 250 4533 13928 870 575 1000 27,668.00
1441 to 1470 7452 0 23368 11371 225 97 200 42,713.00
1471 to 1500 9149 25 13568 3779 750 925 722 28,918.00
1501 to 1530 8021 456 8595 18000 4533 550 0 40,155.00
151 to 180 7371 250 750 0 0 0 14000 22,371.00
1531 to 1560 5910 0 355 12075 1850 0 0 20,190.00
1561 to 1590 1415 750 1998 4500 1600 147 0 10,410.00
1591 to 1620 4141 0 873 4650 675 100 0 10,439.00
1621 to 1650 1002 250 0 0 0 0 0 1,252.00
1651 to 1680 285 0 285 0 0 0 0 570.00
1681 to 1710 1285 0 550 1001 0 0 0 2,836.00
1711 to 1740 1500 0 0 0 175 0 250 1,925.00
1741 to 1770 6927 0 0 0 275 0 250 7,452.00
1771 to 1800 6530 1920 8069 500 0 11561 250 28,830.00
1801 to 1830 785 0 1142 0 0 4007 375 6,309.00
181 to 210 28459 7882 11031 0 0 0 0 47,372.00
1831 to 1860 50 248 285 1075 374 2374 0 4,406.00
1861 to 1890 2067 0 1429 0 300 1228 0 5,024.00
1891 to 1920 134 0 1547 275 0 393 0 2,349.00

Thanks,
BB

 

[GJParker]

You could do a slight tweak to LBass formula

//@DayGroup
numbervar days := {@numberofdays};
numbervar x;
numbervar i;

for i := 4 to 100 do( //replace 100 with an upper limit
if days <= 120 then
x := 120
else if days in (i*30)+1 to (i+1)*30 then
x := (i+1)*30);
x

This will assign a number which when used in your crosstab will be sorted in the correct order, you can then add in a group display formula to display your range i.e.

If {@DayGroup} = 120 Then
'000 to 120'
else
ToText({@DayGroup}-29,'000') & ' to ' & ToText){@DayGroup},000')
ToText({@DayGroup}

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[SkipVought]

Format you ranges to return
[tt]
0000 to 0120
or
0 to 120
[/tt]
with either leading zeros of leading spaces AND assuming that your largest value will never be more than 9999.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lbass]

Yes, just change the format to reflect the greatest number of days possible,e.g, if only thousands:

numbervar days := {@numberofdays};
stringvar x := "";
numbervar i := 0;
for i := 4 to 100 do( //replace 100 with an upper limit
if days <= 120 then
x := "0000 to 0120" else
if days in (i*30)+1 to (i+1)*30 then
x := totext((i*30)+1,"0000") + " to "+
totext((i+1)*30,"0000")
);
x

-LB

 

[bubu2006]

Thanks LBASS.