help with a formula

[ridhirao22]

Hi All,

Using CR2008 and Oracle 10. I have to calculate the change for the report and
don't understand how I can do this or can it be done in CR?.

Sql Query returns data as below for a product.
I need to get the latest revised value where there is a change and muliply by the units on the product

for ex: for the below data
total change column should be calculated as

(0.8496 -0.9445) * 100
//units for the product 90+10 = 100

or

(0.8496*100)-(0.9445*100)

UNITS Original Revised Title LASTDate ID1 ID2 Rebate
90 0.0333 0 Program1 24-Aug-09 715124 132906 0.0333
0 0 0.2361 Program1 02-Nov-09 789151 150022 0.2361
0 0 0.2124 Program1 17-Aug-10 958435 150022 0.2124
10 0.1332 0 Program2 17-Aug-09 714420 132906 0.1332
0 0 0.9445 Program2 02-Nov-09 788357 150022 0.9445
0 0 0.8496 Program2 17-Aug-10 957734 150022 0.8496

I tired to upload the excel file for sample..didn't work.


Any help id much appreciated.

TIA,
RR

 

[lbass]

What is the logic for using .9445? Doesn't the program matter?

-LB

 

[ridhirao22]

From looking at user's calculation which I have replicate in the report. It seems data is by program/lastdate is the matter. 0.9445 a rate and is considered as the intial as there is a rate change for the ID2 and no logic.

I think I need to get the lastest rates based on the program/lastdate if there is only a change(My guess). There are case were the rate is same and not changed. My looking to find more. But wanted to try if I able to get the these.

 

[ridhirao22]

Lbass,
I am getting the Original and Revised columns by checking for a type in the sql for each ID1
decode(type,'ORGI',Rate) as original
decode(type,'RC',Rate) as revised

Please advice.

 

[ridhirao22]

Could I still get the data stored this way? for a product and a program:

SQL data returns the following:

UNITS Original Revised Title LASTDate ID1 ID2 Rebate
135 0.0657 0 Program1 17-Aug-09 714245 132903 0.0657
0 0 1.2939 Program1 2-Nov-09 788272 150020 1.2939
0 0 0.7982 Program1 17-Aug-10 957465 150020 0.7982

Manipulate in crystal as below:
UNITS Original Revised Title LASTDate ID1 ID2 Rebate
135 0.0657 0 Program1 17-Aug-09 714245 132903 0.0657
0 0.0657 1.2939 Program1 2-Nov-09 788272 150020 1.2939
0 1.2939 0.7982 Program1 17-Aug-10 957465 150020 0.7982

TIA,
RR

 

[lbass]

But what is the missing result that you are trying to get? Are you just trying to change the "original" column? It looks like you could replace the Original column with:

if previous(revised) = 0 then
previous(original) else
previous(revised)

-LB

 

[ridhirao22]

Lbass, Thank you for the reply.
May be I am unable to explain the issue any better. Let me try one more time.

I am separating the Original and Revised by checking for a type and displaying Rebate value as orgi and revised. If you look at the above product ex: for ID2
rebate is 1.2939 then there was a rebate change as to 0.7982 in this case the 1.2939 becomes intial rate and .7982 become the active rate. I need to calculate the change impacted on the rate. The problem I am having is the units are by product and the rate is for each ID1.
since the change implies to ID2 I need to find the difference of the (active rate - intial rate)* product units.
I am really have tough time getting this done. I could try any advice.

 

[lbass]

If you group on Product and then on Program, you could use a formula like this:

if {table.lastdate} = maximum({table.lastdate},{table.program}) then
({@mypreviousformula}-{table.revised})*maximum({table.units},{table.program})

...where {@mypreviousformula} is:
if previous(revised) = 0 then
previous(original) else
previous(revised)

-LB

 

[ridhirao22]

Lbass, Could I do the I mean based on ID2 for the ex above:
...where {@mypreviousformula} is:
if previous(revised) = 0 then
previous(original) else
previous(revised)

I tried what you advice but there are cases where there is no rebate change.
I also tried to do the another group by ID2 column

In this case here ID2 is 150020

UNITS Original Revised Title LASTDate ID1 ID2 Rebate
135 0.0657 0 Program1 17-Aug-09 714245 132903 0.0657
0 0 1.2939 Program1 2-Nov-09 788272 150020 1.2939
0 1.2939 0.7982 Program1 17-Aug-10 957465 150020 0.7982

 

[lbass]

I'm not following what the issue is here. Please also explain the fields ID1 and ID2 and what they mean.

-LB

 

[ridhirao22]

Rebate rate column is for each ID1 and the change implies on the ID2 column.

 

[ridhirao22]

Lbass,

I tried to do this and seem and placed it in the details
@IntialRate
If previous({Command.ID2}) = {Command.ID2} then
(
IF ({Command.SUBMITTED}=0 and {Command.REVISED}=0) then 0
else
(
if {Command.SUBMITTED} = 0 then
previous({Command.REVISED})
else
{command.REVISED}
)
)
else
{Command.REVISED}

But I unable to do a max on the group ID2

I am really sorry I am not very clear. Iam try my best to explain this.

 

[lbass]

Do you mean the change applies to the ID2 column? But then what? I don't see anything specific to ID2 that is then affected by the rebate change.

-LB