|
Hi all,
I'm fairly new to Crystal Reports and have not used too many formulas in terms of local variables. I'm running into a problem with a report I'm doing.
A quick snapshot: Our company is wanting to use a forecast to determine egg sizes and quantities and compare them with actual sizes and quantities as well as sale egg sizes and quantities.
I have made a cross-tab that has the egg sizes as columns and in the rows there's the forecast quantity for the egg sizes, percentages of forecast total for each size, the sales quantities, percentages of sales total for each size and then the percentage difference between the forecast and sales.
When the percentage and quantity is negative in the cross tab it shows that we are downgrading egg sizes (eg. a large egg is being put into a medium egg box) which loses us money. In my query I have the gross profit of each egg size.
I was wondering if it was possible to determine the opportunity cost (as in the difference between the gross profits between the large egg size to the medium egg size multiplied by the egg quantity and multiplying the available quantities (as in the leftover egg after sales of the larger egg sizes). Is this possible to do in crystal? I don't know how to do this in the cross tab.
Any help would be greatly appreciated.
Many thanks,
Stevan23
|
|
Hi all,
Just an update. We have simplified the formula to be: ABS(IF(D22<0),(-D22-E22)) in Excel where this is basically saying if the net quantity difference between the forecast, actual and sales for the second most right column (the second largest egg size) then subtract the largest egg net quantity from the second largest egg net quantity and make it an absolute value. This will tell us the quantity downgraded (the largest eggs being packed as second largest eggs). This formula repeats until the smallest egg size where the net quantity is set at 0.
Can this formula be replicated in crystal cross tabs?
Many thanks.
|
IP Logged