Running Total Subtraction on Crysta XI

[TryingToLearn101]

Hi all,

I am currently using Crystal XI professional and I have this issue that I'm not sure how to address. I have 4 fields of information. They are listed like below:

item purchased qty available discount
ABC123 100 25 10%
ABC123 100 80 15%

I am buying ABC100 for a quantity of 100. Now, the first 25 pieces will get 10% discount and the remaining 75 pieces will get 15%. Is it possible to do such logic/formula calculation within Crystal? The number of discounts can be 2 like above or it can be 1 or it can be however many. I need to have the qty purchased basically to take the discount until it is depleted.

I think that's all of the information - let me know if anyone has any input on this.

Thanks in advance.

 

[briangriffin]

I don't think it's too clear what you're trying to do. The two records that you give - is that what is stored in the database, or what Crystal returns? What does the qty available figure represent?

ABC100 - is this a new item? Will the discount thresholds be the same for each item, or are the specific to each item?

Provide a little more detail and we can come up with something that will work for you.

 

[Madawc]

A running-total count would enable you to separate the first 25 from the rest, something like

if {your.count} <= 25 then 10 else 15

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[TryingToLearn101]

Thank you for the replies -

I made a huge mistake on my explanation. They are all supposed to be ABC123 and there's no ABC100.
The 2 records of item ABC123 are what's in the database. Basically, what happens is in day-to-day basis, I can sell item ABC123 to my customers. But when I sell it to them I can either pick the first item ABC123 that has 10% discount or I can pick the second item ABC123 that has 15% discount. Nevertheless, my customer actually buys 100 pieces. That means I have to use both of the ABC123 records to fulfill that. What I need to do is I need to allocate that 100 inquiry from the 1st item ABC123 with 10% and then the 2nd item ABC with 15%. I guess it is like first in first out.

So at the end, I want to end up with item ABC123 25pc at 10% and item ABC123 75pc at 15% to give to my customer and I still have 5pc left in the warehouse of item ABC123 at 15% discount. This way I can calculate the value of the price they have to pay. It always have to go from top to bottom (again like first in first out).

What I am struggling is that the customer inquiry of 100pc is coming from a different table that if I link, will look like below in the purchased column. But take a look at my desired result column. It shows 25 and 75 that are summed up to be the total 100pc with the correct discount allocation. How many pieces of the 100 are getting 10% disc and how many pieces of the 100 are getting 25% disc.

item purchased qty available discount desired result ABC123 100 25 10% 25
ABC123 100 80 15% 75

Again, thank you in advance for any input you might have.

 

[Madawc]

If the difference is based on details, then a pair of running totals should let you count numbers. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10