Almost Same Data Diffrerent Date 2

[willyd61]

Hello All,

I am having trouble thinking of how to attack this problem, I am using CR9 against Oracle 9i.

There is 1 table that I load with 7 fields. 1st Field is a Date field, then a ID followed by currency fields.

Every month I load this table with the current records, and want to keep the old stuff in, hence why I threw the date field in. All the rest of the field will most of the time be exact.

What I want to do is treat each month of data as a different subset of data, then compare some currency fields, is they change I print that record with all of the fields, and the % of change.

The data looks like this

Date, RecordID, Currency1, Currency2, Currency3

These are monthly bills coming in that I parse with perl and insert every month, I want to identify the ID's that increase or decrease in price.


Thanks,
-WL

-Willy D

 

[synapsevampire]

To paraphrase, you want to compare current month amounts to the previous months amount and if they differ show them. Or are you trying to do a historical across multiple months?

I would group by the ID and then the month. Then in the Report->Edit Selection Formula->Group place something like:

minimum({table.amount},{table.date}) <> maximum({table.amount},{table.date})

A wild guess though, try posting technical information:

Example data
Expected output

-k

 

[willyd61]

Example Data: (This is just a small clip of data)
Date - Circuit_ID Amount1 - Amount2
09/01/04 - 109 - $60.05 - $12.52
09/01/04 - 112302 - $25.50 - $29.92
09/01/04 - 232321 - $23.12 - $231.00
09/01/04 - 321321 - $21.11 - $121.00
10/01/04 - 109 - $60.05 - $12.52
10/01/04 - 112302 - $25.70 - $29.92
10/01/04 - 232321 - $23.12 - $231.50
10/01/04 - 321321 - $21.11 - $121.00

Expected output:
Circuit_ID - AmountDiff - %Change
1112302 - .20 - 0.01

Any record where the amount has not changed I dont want shown, I want the report to look at the current month against last months data, in the future I would like to have
a price delta across all months, but for now only current month against last month.

 

[Madawc]

If that date is mm/dd/yy, then you can group by Circuit_ID, selecting records for this month and last month. Do a maximum and minimim for each group, and use Group Selection to suppress groups where these are the same.

Madawc Williams (East Anglia, UK)

 

[willyd61]

I have never done a Max/Min on each group, do u mean make a formula for each curreny field I want to compare on the group selection and then compare these 2 in the group selection criteria??

 

[Madawc]

There are several ways tot total: running totals, summary totals, grand totals and variables. Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total. Or else use the Field Explorer, the icon that is a grid-like box.
It is also possible to get get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.
Running totals allow you to do clever things with grouping and formulas. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.
Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.

In your case, you want to do a pair of summary totals, selecting Maximum and Minimum.

Madawc Williams (East Anglia, UK)

 

[willyd61]

Thanks, I am almost complete,

What I did was put the right clicked and did a summary on 1 of the currency fields, with min and max, put this in the group footer, and put in the group selection for the report that these can not equal eachother.

I seem to still get ones that min and max equal....

Also, I seem to remeber being able to say for the record selection for the date stuff something like, record_date > current_date-60 to select all records newer then 60 days. I am trying to dynamic pull these records, as I dont want to have a parameter, etc....

Thanks for your help madawc!

 

[Madawc]

I'm surprised Group Selection is failing, but you can also suppress individual sections.

Have you displayed the values you think are failing? Data has a way of being surprising.

Madawc Williams (East Anglia, UK)

 

[willyd61]

Got it. I know have the record's that the min and max are not the same for the currency amount I am comparing. I realized though that I can't determine when the amount changed. I would like to know if last months record to this months records was more expensive or cheaper..... Once I do this I can start to put %'s, etc.....

I have been playing around with it, but no luck.

thanks again for you help....

 

[willyd61]

Bump.....


I realized though that I can't determine when the amount changed. I would like to know if last months record to this months records was more expensive or cheaper????

 

[willyd61]

bump

 

[Madawc]

You can group records by month, using Month(Date). And maybe also Year(Date); combine the two in a formula and group by that formula.

Madawc Williams (East Anglia, UK)

 

[willyd61]

Not sure what you mean, what I have now is this.

The fields mentioned above, with a record selection of the past 2 months. Then I group by RecordID. This gives me pairs of records.

Then I do a min/max on the currency field I want to compare, then I use a group selection to suppress any records that min/max are the same.

Now, I am left with just one record that I suppose is random (dont know if its the min records or max record). So the formula's I am creating now showing % of increase/change, etc is skewed to the fact is I have no idea what direction the price changed?

Make sense?

 

[willyd61]

bump

 

[lbass]

If there is just one record per month per circuit ID, then group by {table.circuitID} and then create formulas like {@chgcurr1}:

if {table.circuitID} = previous({table.circuitID}) then
{table.currency1} - previous({table.currency1})

For {@percentchange} use:

{@chgcurr1} % previous({table.currency1})

You can display these in the detail section, or since there are only two records per ID, place them in the group footer.

-LB

 

[willyd61]

Hmm.... That does work but I dont't seem to get any negative values.

I am consfused big time now. I guess the easiest thing to get me back on track is to see see both months value and mark it.

THis way I can see
(recordid month1.cur1 month2.cur2 chcurr1 %curr)

Know what I mean? Thanks for your help.

 

[Madawc]

If you don't get any negative values, check that the display field is able to show them.

Madawc Williams (East Anglia, UK)

 

[willyd61]

any idea on how to make this happen?

(recordid month1.cur1 month2.cur2 chcurr1 %curr)

 

[lbass]

Group on recordID, with no group on date. Then create formulas like:

//{@Jan}:
if month({table.date}) = 1 then {table.currency1}

//{@Feb}:
if month({table.date}) = 2 then {table.currency1}

Since there is only one record per month, you can insert maximums on each of these formulas by right clicking on them->insert summary->maximum. Then change your diff formula to:

maximum({@Feb},{table.recordID}) - maximum({@Jan},{table.recordID}) % maximum({@Jan},{table.recordID})

-LB