How to pick a result from a formula and use it further?

[diagonal76]

Hi, I'm trying to make some formulas to calculate some differences between prices. I have the following information: item, order date, price and delivered qty. I would then also calculate order value based on price and delivered qty.

What I would like to do is to compare the last purchase prices from last year against the purchase prices from this year (also with values instead of just % changes in the prices).

item price order date delivered qty
A1 41,00 5.10.2005 30
A1 41,00 5.10.2005 30
A1 41,00 10.10.2005 30
A1 41,00 10.10.2005 18
A1 50,00 10.2.2006 11
A1 61,40 17.2.2006 7

How could I create such a formula in Crystal Reports? Basically in the example case it would compare price of item A between 10.10.2005 (max order date from last year)and orders from 2006. The calculation is theoretical as it would use the 2006 deliveries multiplied with 2005 prices to demonstrate whether the price level is going up or down. Example result returned by formula would be:

Price variance = ([50*11 + 61,40*7] - [(7+11)* 41]) / (50*11 + 61,40*7) * 100% = 24,7 % increase in item price. The reason why value is used is that items would be grouped together based on their hierarchy so order values are needed in analysis.

 

[dgillz]

I cannot follow your logic. Instead of throwing numbers around, please tell us what fields and record numbers you want to manipulate, and how to manipulate them.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"A fine is a tax for doing wrong. A tax is a fine for doing well.
" - unknown

 

[diagonal76]

1) I need a way to identify what is the value of field 'price' for the row with the maximum value of field 'date' within year 2005. This is to identify the item price for the last order from last year. Price 41 in the example.

2) The value of deliveries for the year 2006 is not too difficult - part ([50*11 + 61,40*7] of the formula

3) After identifying the item price from last year (point 1) this needs to be matched with point 2 - part [(7+11)* 41]) of the formula - this is the difficult part

I don't know how to re-use the value 41 in point 3. Question is how to get this value 41 shown for all rows so that it could be used in further calculations.

I tried: if {orderdate} = {@max orderdate from 2005} then {price} but that only gives the result 41 for rows 3 and 4 in the example (the real syntax was a bit more complicated). If this value 41 could be returned for rows 5 and 6, then it would be possible to calculate [(7+11)* 41]) and the rest of the price variance formula.

 

[IdoMillet]

Sounds like you need to use a SQL Expression or a subreport with a shared variable passed back to the main report.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

First insert a group on {table.item} and then a second group on {table.date} on change of year. Then create a formula {@max05price}:

whileprintingrecords;
numbervar price05;

if year({table.date}) = 2005 and
{table.date} = maximum({table.date},{table.date},"annually") then
price05 := {table.price};

You could then reference this in a formula in a later section:

whileprintingrecords;
numbervar price05;
numbervar subtot06at05price;

if year({table.date}) = 2006 then
subtot06at05price := subtot06at05price + (price05 * {table.qty});

Then create a display formula for the item group footer:

whileprintingrecords;
numbervar subtot06at05price;

If {table.price} is a currency, change the variables to currencyvar.

-LB

 

[diagonal76]

Thanks lbass, I got quite far with this so now this works for items that have purchase orders for both years 2005 and 2006.

However, when the price ({@max05price) is missing from year 2005 then CR uses the value from the previous item and so the calculation goes wrong.

How could I define that these calculations happen within the item group that I have created? Item grouping is currently the highest level and date grouped by each year is the second group. I did try to place a formula in the item footer but the result is the same. Is 'display' formula a specific type of formula in CR or just a verbal expression you used?

 

[lbass]

Sorry about that. You should create a reset formula for the item group header:

whileprintingrecords;
numbervar price05 := 0;
numbervar subtot06at05price := 0;

-LB

 

[rexie]

Hi all,

I have three tables,
1. Part Masster (Contains the part Numbers)
2. Payable header (contains the Invoice date)
3. Payable Line (contains Invoice price), every line joins to Purc_order_line table)
4. Purc_order_line table (gets the Part Number join to Part table)

I would like to run create a crystal report to get the First Invoice Price and the last Invoice Price.

any help would highly appreciated.
thanks a lot in advance