Formula help

[staceyn]

Hello,
I am using Crystal 10 with an informix database.
I am trying to determine how to make a field an integer field in Crystal. Here is what I am coming up against.
I am trying to create a formula that evaluates prior retail weeks. In the time2 table we have a weekcode field that is set as a number. I have successfully written a formula that will return the appropriate weekcode in the report.
As an example, the current weekcode in the table is 401. I have written a formula that says if the last purchase
date = business date from time2 table, then return the weekcode.
Then another formula that says take the returned weekcode and subtract 1, to give me the prior weekcode.
Then another formula that says if the returned weekcode from the above formula
is = time2.weekcode then show me the on hand values for that week.
Here is where I am running into a problem. The current weekcode is 401. When I run the formula to subtract 1 from that value, I get a returned value of 400.00.
When I use the formula to compare the returned value to the weekcode field I don't get any results since 400.00 does not match 400.
Sorry about rambling here, hope this makes sense.

Any ideas on how to get around this?

Thanks,

 

[satinsilhouette]

Could you paste the formula here so we can modify it to help you out.

Thanks so much!
satinsilhouette

 

[1973kk]

why don't you do totext(formulaname, 0) and then compare

 

[synapsevampire]

Chatting about formulas instead of posting them wastes time.

As 1973 points out, ast some point you're likely using a totext, so form the totext as:

totext({table.field},"",0)

Hard to say though, not sure why you think describing a formula is better than just posting it.

-k

 

[staceyn]

Thanks!

Here are the formulas:

@Purch_week:
if {inv.inv_last_pur} = {time2.businessdate} then {time2.fweekcode}
@current_week:
if {time2.businessdate} = currentdate then {time2.fweekcode}
@current_pur_week:
if {@purch_week}={@current_week} then {time2.fweekcode}
@oh_current_week:
if {@purch_week}={@current_week} then {@total_oh}

The above all work fine with no issues

@week_1:
{@current_week}-1
@week1_oh:
if {@Week_1}= {time2.fweekcode} then {@total_oh}

This is where I am having the problem.
week_1 returns a value of 400.00
The week1_oh formula is returning no values since it is comparing 400.00 to the value of 400 which is what is held in the table.

 

[synapsevampire]

If {time2.fweekcode} is a numeric, then you're mistaken, the output of the formula has nothing to do with your problem.

they are both numerics, and how Crystal displays them has nothing to do with whether a numneric = a numeric. If they were different data types, then the formula would error.

So, the issue is elsewhere, perhaps in what is in {@total_oh}

You didn't share that.

-k

 

[staceyn]

K-
Thanks for the input, any additional help would be greatly appreciated.
When I place these formulas on my report, I get values for everything except when I do the compare as mentioned above.
If I add another formula that simply returns the weekcode, instead of the total_oh "if {@Week_1}= {time2.fweekcode} then {time2.fweekcode}" I am also not getting any results although I do see results on the report for the {@Week_1}formula itself.
If I use the totext function, then I wouldn't be able to subtract 1 from each of these values, so I am not sure if that would work.
Once I have this part figured out, I have to replicate the formualas to show aging categories for the last 13 retail weeks.

The @total_oh is simply a sum of on hand values by size. Below is the actual formula.

{inv_detail.id_size1}+{inv_detail.id_size2}+{inv_detail.id_size3}+{inv_detail.id_size4}
+{inv_detail.id_size5}+{inv_detail.id_size6}+{inv_detail.id_size7}+
{inv_detail.id_size8}+{inv_detail.id_size9}+{inv_detail.id_size10}+{inv_detail.id_size11}
+{inv_detail.id_size12}+{inv_detail.id_size13}+{inv_detail.id_size14}+{inv_detail.id_size15}
+{inv_detail.id_size16}+{inv_detail.id_size17}+{inv_detail.id_size18}+{inv_detail.id_size19}
+{inv_detail.id_size20}+{inv_detail.id_size21}+{inv_detail.id_size22}+{inv_detail.id_size23}+{inv_detail.id_size24}

 

[lbass]

I think you should lay out your date formulas in the detail section to see how they relate to each other. The currentweek formula will ONLY result in a week code when the business date matches the currentdate, even though you are using it to show the week before in your week1 formula. I think you are trying to use this to look at other weeks of business dates, but that won't work, because the results won't be in the same row. Instead try basing the week code on the value of the currentdate, i.e., for currentweek use:

datepart("ww",currentdate)

Last week would be:
datepart("ww",currentdate)-1

Or adjust these so they match your fweekcode by adding 355 or something.

-LB