Print Page | Close Window

Need help with a formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17341
Printed Date: 05 May 2024 at 2:15am


Topic: Need help with a formula
Posted By: Wearing
Subject: Need help with a formula
Date Posted: 16 Aug 2012 at 4:54am

Hi There

I’m hoping someone can help me here.  I’m trying to setup a formula to calculate the actual unit price when a lot price is given.  Someone suggested to use a subreport to lookup if there is a “lot price” in the sequence but that’s not working….any suggestions

                                    QTY                Unit price         Actual unit price

ALB1                           4                      6.74                             4.58    

ALB2                           10                    0.98                             0.67

Lot price                      1                      25.00


Replies:
Posted By: DBlank
Date Posted: 16 Aug 2012 at 5:21am
you will have to explain your data source/structure quite a bit more for anyone to be able to really help.

Posted By: Wearing
Date Posted: 16 Aug 2012 at 6:46am
I'm not quite sure what that it is.  The data is held in a ddf file and it just pulls in these fields of information (Inventory, Qty, Price).  Then I tried to set up the formula's so that it would "lookup" to see if "lot price" was in the inventory listing, if so it was to display the unit price. (which it does) but then to calculate the actual unit price it is working into the formula the "lookup" the lot price formula (because I need this number to do the calculation) and is returning 0.00 for the items except the actual lot price line item...does that help?

Posted By: DBlank
Date Posted: 16 Aug 2012 at 7:01am
so the lot price is stored ont eh same row and you are not having to join any tables, correct?
if so please post some sample data rows and what you want it to do based on those rows and what value you want it to return for each row.
if not please explain how the lot price field is set up in your data set.

Posted By: Wearing
Date Posted: 16 Aug 2012 at 7:35am
That's correct.  The lot price is an inventory item the same as the other two items.  I can't post in a picture of what the data looks like but this is how it reads:
 
Sales Order Detail
     Number
     Rec No
     WHSE
     Code
     CMTQTY
     BuyUOM
     UnitPrice
 
To set up what I have in my initial post, I have the following:
 
Code       CMTQTY           Unitprice
 
Then I have the formula (this brings back 25):
 
If Code=Lot Price, then (bring back) unit price, else 0
 
I need to calculate the total price for each line so I have this formula (this brings back 26.96, 9.80 and 25):
 
CMTQTY*Unitprice
 
Then subtotal these items:
 
Sum(Extended Price)
Sum(CMTQTY -1)
 
To calculate the first extended prices though (18.33, 6.66 and 17) I need to refer back to the formula that gave me the lot pricing (25), however, when I reference that field it again it does the "lookup" part again and because the ALB1 is not "lot price" it is calculating 0.00 for this whole column of information except for the "lot price"
 
This is how the formula calculates in excel
 
                     QTY                      Unit Price  Extended   Total           Equals
                                                                    Price         Extended   the unit
                                                                                    based on    Price
                                                                                    LP of 25
                      
ALB1

4

0

6.74

26.96

 18.33515

4.583787

ALB2

10

0

0.98

9.8

6.664853

0.666485

Lot Price

1

25

25

25

17.00218

17.00218

 

 

 

 

 

 

 

 

14

25

 

36.76

42.00218
    

Posted By: DBlank
Date Posted: 16 Aug 2012 at 8:43am
I am having trouble tracking your process here
Anyone else want to jump in?

Posted By: Wearing
Date Posted: 16 Aug 2012 at 9:02am
        QTY                                       Unit   Extended   Total              Equals
                                                     Price  Price           Extended      the unit
                                                                                 based on       Price
                                                                                 LP of 25
    
 

ALB1  4          =IF(A1="Lot Price",D1,0)    6.74     =B1*D1       =(E1/$E$5)*$C$5     =F1/B1  ALB2 10        =IF(A2="Lot Price",D2,0)    0.98      =B2*D2       =(E2/$E$5)*$C$5     =F2/B2

Lot Price 1    =IF(A3="Lot Price",D3,0)    25         =B3*D3       =(E3/$E$5)*$C$5     =F3/B3

                                                                                               

     =SUM(B1:B4)-1   =SUM(C1:C3)                =(E1+E2+E3)-C5   =SUM(F1:F4)         
 
 
This is how the formula looks in excel...does this help?  I'm trying to determine what the unit price is of each item based on the lot price.

Posted By: Wearing
Date Posted: 16 Aug 2012 at 9:02am
Sorry that didn't come through very well


Print Page | Close Window