Eliminating duplicates in Detail Section - A Tricky one 1

[Nansat]

Problem Description

Detail section records:

Item store salesdate price price_eff_dt price_exp_dt
001 003 23-Apr-2006 $1.0 1-Oct-2005 30-Dec-2005
001 003 23-Apr-2006 $1.5 1-Apr-2006 30-Apr-2006
001 003 23-Apr-2006 $2.5 1-Apr-2006 NULL
001 003 23-Apr-2006 $1.5 1-Aug-2006 30-Sep-2006

Item, store, salesdate, price_eff_dt and price_exp_dt are Table fields.

PRICE is the crystal formula that determines the product price (based on sales date that falls in PRICE_EFF_DT and PRICE_EXP_DT) for that product and store combination.

The requirement now is, once I determine the valid price for a product and store combination (record 2 in the above example), I want to set the price as ZERO for the rest of the records for the SAME PRODUCT & STORE combination.

I tried definining global variable..etc. Nothing really worked for me so far, since its in detail section and the global variables are getting reset.

Any help will be sincerely appreciated.

Thanks, Nansat

 

[huggyd]

pseudo code here but i would go for something like this as a formula for price

If printdate >= PRICE_EFF_DT AND printdate <= PRICE_EXP_DT)
then price
else 0

may not work if you don't have a valid date range for a product store combination

 

[lbass]

Why is record two the valid price and not record three?

-LB

 

[Nansat]

Thanks for the response.

I am calculating PRICE using the formula

If salesdate >= PRICE_EFF_DT AND salesdate <= PRICE_EXP_DT)
then price
else 0

But the problem with this is, in some cases when the sales date doesnt fall in any range, then the PRICE will be the RECORD that has NULL PRICE_EXP_DT.

In the above example both row 2 and row 3 are valid. But my requirement is, once I have determined PRICE in row 2, I want to skip or make the PRICE calculation for rest of rows as ZERO for SAME ITEM,STORE and SALESDATE combination.

Item store salesdate price price_eff_dt price_exp_dt
001 003 23-Apr-2006 $1.0 1-Oct-2005 30-Dec-2005
001 003 23-Apr-2006 $1.5 1-Apr-2006 30-Apr-2006
001 003 23-Apr-2006 $2.5 1-Apr-2006 NULL

 

[lbass]

This doesn't really answer the question. It appears that you are saying whichever range that the date falls into that comes first will determine the price, but the sort can be changed, as in:

Item store salesdate price price_eff_dt price_exp_dt
001 003 23-Apr-2006 $1.0 1-Oct-2005 30-Dec-2005
001 003 23-Apr-2006 $2.5 1-Apr-2006 NULL
001 003 23-Apr-2006 $1.5 1-Apr-2006 30-Apr-2006

If you mean that you want to use the row that has an enddate if the sales date is greater than the effective date, but if there is no enddate in any row that meets the effective date criterion, you would then use the price in the null enddate row, then create a formula {@enddate}:

if {table.salesdate} >= {table.price_eff_dt} then {table.price_exp_dt}

Then you could insert groups on item, store, and salesdate, and then create a second formula:

if {@enddate} = maximum({@enddate},{table.salesdate}) then {table.price}

Assuming that nulls equate to date(0,0,0), if the null value is the only enddate value where the effective date meets the criterion, that price will be used, but if any other rows have a non-null enddate, the maximum date will be used--since this will be greater than date(,0,0,0), this would have priority over the null date.

-LB

 

[Nansat]

1. I have already Sorted the records after reading from the database using cystal Report->Record Sorting option.

Under ITEM grouping, The records are being sorted based on

Store -> Ascending order
Salesdate -> Ascending order
price_eff_dt -> Ascending Order
price_exp_dt -> Descending order

So the records will be in the following order

Item store salesdate price price_eff_dt price_exp_dt
001 003 23-Apr-2006 $1.5 1-Apr-2006 30-Apr-2006
001 003 23-Apr-2006 $1.0 1-Oct-2005 30-Dec-2005
001 003 23-Apr-2006 $2.5 1-Apr-2006 NULL

I have created PRICE formula that determines PRICE for a ITEM on a particular 'Salesdate' for PROD-STORE combination.

***IF Sales date is between price_eff_dt AND price_exp_dt THEN the price would be pickedup without any problems.

However if sales doesnt doesnt fall in between any price_eff_dt AND price_exp_dt then price will be determined as the PRICE of a RECORD whose price_exp_dt is NULL.

Catch is this has to happen in the detail section because, I have another running total that does the total$sales based on the price and quantity sold on each SALES DATE.

SO MY JOB IS DONE, IF I CAN DETERMINE JUST ONE PRICE FOR A PRODUCT,SCANDATE,STORE combination and ignore the rest.

Hope I have explained the situation clearly.

 

[lbass]

Did you try my suggestion? It should do what you intend.

-LB

 

[Nansat]

I tried your suggestion, But I am getting the following error "A Running Total Cannot refer to a print time formula."


Because what I am donig is, once identifying the PRICE, I am using that price to calculate the running total of (price* Quantity)

 

[lbass]

Please reread my post. I did not suggest the use of running totals. I don't think a running total would work for what you are trying to do.

-LB

 

[Nansat]

As per your valuable suggestion, I could calculate the price.

My next requirement is, I have to use this price, to calculate total$(summary) amount by multiplying it with the quantity sold.

Here is the record structure


Item store salesdate price prc_eff_dt prc_exp_dt Qty
001 003 23-Apr-2006 $1.5 1-Apr-2006 30-Apr-2006 40
001 003 23-Apr-2006 $1.0 1-Oct-2005 30-Dec-2005 40
001 003 23-Apr-2006 $2.5 1-Apr-2006 NULL 40
001 004 23-Apr-2006 $1.6 1-Apr-2006 30-Apr-2006 72
001 004 23-Apr-2006 $1.2 1-Oct-2005 30-Dec-2005 72
001 004 23-Apr-2006 $2.5 1-Apr-2006 NULL 72


To calculate the Total$ Amount, I am trying to use the formula (Calcualted PRICE * Qty), in the running total.

While doing so, I am getting the error "A Running Total Cannot refer to a print time formula."

 

[lbass]

In your example, is the quantity repeating itself, so that you only want one instance of 40 * the price, and one instance of 72 * the price and then add them? If so you can set up these formulas:

whileprintingrecords;
currencyvar price;
currencyvar qtyxprice;

if {@enddate} = maximum({@enddate},{table.salesdate}) then
price := {table.price} else
price := 0;
qtyxprice := qtyxprice + (price * {table.qty});

Since the price will be zero in all but one row, the calculation will occur only once per store/item/salesdate.

Then use a display formula in the report footer:

whileprintingrecords;
currencyvar qtyxprice;

If you want these calculations at a group level, then you would need a reset formula in the corresponding group header:

whileprintingrecords;
currencyvar price := 0;
currencyvar qtyxprice := 0;

If your price formula returns a number, not a currency, then change all the "currencyvar" to "numbervar".

-LB

 

[Nansat]

Thank you very much Lbass. My job got done.

 

[Nansat]

Hi lbass,

I am opening this post once again because, of the problem that I am running into.

Sales data is grouped by ITEM and SALESDATE and then SORTED BY STORE ASC,
price_effdt ASC,
price_expdt DESC

i tried your suggestion, that calculates the endprice, however it is not working for all the cases.

---your suggestion --

{@enddate}:

if {table.salesdate} >= {table.price_effdt} then {table.price_expdt}

{@endprice}:

if {@enddate} = maximum({@enddate},{table.salesdate}) then
price := {table.price} else
price := 0;
qtyxprice := qtyxprice + (price * {table.qty});
---your suggestion --

The case for which it is not working is..

Item store slsdate price prc_effdt prc_expdt Qty
001 003 23-Apr-06 $2.0 17-Apr-06 NULL 10
001 003 23-Apr-06 $1.0 14-May-06 03-Jun-06 10
001 003 23-Apr-06 $0.99 04-Jun-06 17-Jun-06 10

 

[lbass]

How is it not working? What is the formula result that is incorrect? What do you think it should be?

-LB

 

[Nansat]

whats happenning in this case is, the endprice is becoming ZERO.

if {@enddate} = maximum({@enddate},{table.salesdate}) then
price := {table.price} else
price := 0;

Reason being, {@enddate} has NULL value where as table.salesdate is '23-Apr-2006'.

 

[lbass]

Okay, then do one of these two things:

1-Go to report options and check "convert nulls to default values" or
2-Create a formula {@expdt}:

if isnull({table.price_expdt}) then date(0,0,0) else
{table.price_expdt}

Then change {@enddate} to:

if {table.salesdate} >= {table.price_effdt} then {@expdt}

-LB

 

[Nansat]

Can you please help me with this doubt regarding this suggestion ?

for the same example:

Item store slsdate price prc_effdt prc_expdt Qty
001 003 23-Apr-06 $2.0 17-Apr-06 NULL 10
001 003 23-Apr-06 $1.0 14-May-06 03-Jun-06 10
001 003 23-Apr-06 $0.99 04-Jun-06 17-Jun-06 10


{@expdt}: Will return Date(0,0,0)

{@enddate}: Will return Date(0,0,0)

And {@endprice}: will return ZERO
because Max(Date(0,0,0),'23-Apr-2006') will be salesdate.

{@endprice} Formula:

if {@enddate} = maximum({@enddate},{table.salesdate}) then
price := {table.price} else
price := 0;

 

[lbass]

{@endprice} will return 0 except for the row that contains the maximum {@enddate}, which is 17-Jun-06. The part of the formula that reads:

if {@enddate} = maximum({@enddate},{table.salesdate}) then...

is checking for the maximum {@enddate} within the salesdate group, i.e., {table.salesdate} is the group condition, not a comparison date. If you wanted to compare two sets of dates, the maximum function would be used like this:

maximum([date1,date2])

-LB

 

[Nansat]

I think, the problem might be,

Maximum({@enddate},{table.Salesdt}) will always returning the maximum @enddate for a {table.Salesdt}

where as I have records like this

Item store slsdate price prc_effdt prc_expdt Qty
001 001 23-Apr-06 $2.0 17-Apr-06 13-May-06 10
001 001 23-Apr-06 $1.0 14-May-06 03-Jun-06 10
001 001 23-Apr-06 $0.99 04-Jun-06 17-Jun-06 10
001 003 23-Apr-06 $2.0 17-Apr-06 NULL 10
001 003 23-Apr-06 $1.0 14-May-06 03-Jun-06 10
001 003 23-Apr-06 $0.99 04-Jun-06 17-Jun-06 10

Now, Maximum({@enddate},{table.Salesdt}) returns '13-May-2006', without having STORE level reset.

Hence while processing 4th record, the {@endprice} is being returned as ZERO.

({@enddate} is date(0,0,0) and maximum({@enddate} is '13-may-2006')

can we get around this problem, without going for a STORE level grouping???

 

[lbass]

I assumed you had outer groups on store and item. Why do you want to avoid this? The group headers and footers can be suppressed.

However, I am unclear on another issue. The 4th record SHOULD return 0, since June 17, 2006 is the maximum enddate. This is how the above results should display the cumulative qtyxprice after inserting groups on store, item, and salesdate:

Item store slsdate price prc_effdt prc_expdt Qty QtyxPrice
001 001 23-Apr-06 $2.0 17-Apr-06 13-May-06 10 0
001 001 23-Apr-06 $1.0 14-May-06 03-Jun-06 10 0
001 001 23-Apr-06 $0.99 04-Jun-06 17-Jun-06 10 9.90
001 003 23-Apr-06 $2.0 17-Apr-06 NULL 10 9.90
001 003 23-Apr-06 $1.0 14-May-06 03-Jun-06 10 9.90
001 003 23-Apr-06 $0.99 04-Jun-06 17-Jun-06 10 19.80

Only row 3 and row 6 would return a non-zero amount to be accumulated.

-LB