Montly rating using the most recent value if no data for the month

[sbf]

Hi Everyone, I need help !
I have the following records in a table:
GlAccont Rating Ratingdate
1111 4 22-JAN-2003
1111 3 13-FEB-2003
1111 4 05-MAY-2003
1111 2 06-JUL-2003
2222 3 12-DEC-2002
2222 2 07-MAY-2003
2222 4 07-JUL-2003
2222 1 08-JUL-2003
I want to group my records by GlAccount , and show the rating values for each of the last 7 months and sum the ratings per month.
If the is no records in the actual month the most recent Rating value for the GlAccount shall be shown.
ex. for GlAccount 1111 there is not a record for APR-2003 and therefore the rating for 13-FEB-2003 shall appear in the column for APR-2003.
Report output:
Rating
Glaccount JAN-03 FEB-03 MAR-03 APR-03 MAY-03 JUN-03 JUL-03
1111 4 3 3 3 4 4 2
2222 3 3 3 3 2 2 1
----------------------------------------------------------
Sum 7 6 6 6 6 6 3

Is that possible? I am using CRW 8.5 / 9. Thanks in advance, sbf-kla

 

[synapsevampire]

A slight variation on the old theme that you want to show data where none exists.

In your case you want to produce erroneous data.

Since your exsample data shows Dec, 2002 data, and Jun and Jul 2003, yet the example output does not, I'm a little unclear of the intent, I'll assume it's an oversite.

I think that you need to produce manual formulas for the months of interest, grouping the report by GIAccount, with these formulas in the details (hidden):

@Jan1
whileprintingrecords;
numbervar LastValue;
If month({ratingdate}) = 1 then
lastvalue := ({rating});
lastvalue
else
lastvalue

For each month...

Then lay out these results across the group footer.

This should get you in the right direction.

I'd generate the data set in a mini Datamart to provide all of the data points in a Table/SP/View as Crystal doesn't lend itself to fabricating data well.

-k

 

[sbf]

Thanks for your reply synapsevampire.

Ups, formatting problem on Report output columns, the rating values have slipped to far to the left side, I will try again:

Rating
Glaccount JAN FEB-03 MAR-03 APR-03 MAY-03 JUN-03 JUL-03
1111 4 3 3 3 4 4 2
2222 3 3 3 3 2 2 1
-----------------------------------------------------------------------------
Sum 7 6 6 6 6 6 3


Yes, the topic has been on the list several times, but due to lack of programming experience I have not been able to use this.
I am able to get the right result for one month by grouping on GlAccount, order by ratingdate and suppressing the detail sec, but I want the result for the last 7 month in one report.

The GLAccont no. is equal to an piece of equipment in our plant.
Every equipment has a ratingvalue indicating the condition of the equipment.
Rating 1= excellent condition , Rating 5 = poor condition
Only when the condition of piece of equipment is changed, a new record is entered in the database. So the condition for 1111 was 3 on 12 dec 2002 and remains 3 until may 2003.

By the end of each month the total condition of the plant is represented by the sum and average of all ratings.
I want to see the condition 7 month back in time in order to see the trend of the condition.

What do you mean by ‘Then lay out these results across the group footer’ ?.
Thanks again

 

[sbf]

Report output again!

Rating
Glaccount JAN-03 FEB-03 MAR-03 APR-03 MAY-03 JUN-03 JUL-03
1111 4 3 3 3 4 4 2
2222 3 3 3 3 2 2 1
-----------------------------------------------------------------------------
Sum 7 6 6 6 6 6 3

 

[lbass]

I think you could group on GLAccount and then do a series of formulas like these:

{@Jan}:

if {table.date} in dateadd("m", -7, date(year(currentdate),month(currentdate),01)) to dateadd("m",-6,date(year(currentdate),month(currentdate),01))-1 then {table.rating}

{@Feb}:

if {table.date} in dateadd("m", -6, date(year(currentdate),month(currentdate),01)) to dateadd("m",-5,date(year(currentdate),month(currentdate),01))-1 then {table.rating} else {@Jan}

Note that after the first formula each subsequent formula should use the previous formula for the last "else"--"else {@Jan} should be the last element of {@Feb}, and "else {@Feb} should be the last element of {@Mar}.

Then you should be able to insert summaries on these formulas to get results per month across all GLAccounts.

-LB

 

[sbf]

Thanks Ibass

Ok I’ve made 8 formulas starting with{@Dec}, (data starts in dec) and group on GLAccount and inserted summaries on these formulars
{@Dec}
if {KLA_RATING.RATINGDATE} in dateadd("m", -8, date(year(currentdate),month(currentdate),01))
to dateadd("m",-7,date(year(currentdate),month(currentdate),01))-1 then {KLA_RATING.RATING}

{@Jan}
if {KLA_RATING.RATINGDATE} in dateadd("m", -7, date(year(currentdate),month(currentdate),01))
to dateadd("m",-6,date(year(currentdate),month(currentdate),01))-1 then {KLA_RATING.RATING} else {@Dec}
…………….. etc…
{@Jul}
if {KLA_RATING.RATINGDATE} in dateadd("m", -1, date(year(currentdate),month(currentdate),01))
to dateadd("m",0,date(year(currentdate),month(currentdate),01))-1 then {KLA_RATING.RATING} else {@Jun}

and gets the following output, but if it is possible somehow to exclude all values except the latest value > 0 in each column in the detail sec, the result will be correct !

JAN-03 FEB-03 MAR-03 APR-03 MAY-03 JUN-03 JUL-03
(GH1) 1111
-----------------------------------------------------------------------------
1111 4 4 4 4 4 4 4
1111 0 3 3 3 3 3 3
1111 0 0 0 0 4 4 4
1111 0 0 0 0 0 0 2
-----------------------------------------------------------------------------
(GF1) 4 7 7 7 11 11 13
-----------------------------------------------------------------------------
(GH1) 2222
-----------------------------------------------------------------------------
2222 3 3 3 3 3 3 3
2222 0 0 0 0 2 2 2
2222 0 0 0 0 0 0 4
2222 0 0 0 0 0 0 1
-----------------------------------------------------------------------------
(GF1) 3 3 3 3 5 5 10
-----------------------------------------------------------------------------
(RF) 7 10 10 10 16 16 23
-----------------------------------------------------------------------------

 

[lbass]

Go to report->edit selection formula->group and enter:

{KLA_RATING.RATINGDATE} = maximum({KLA_RATING.RATINGDATE},{table.GLAccount})

This will return only the most recent record for each GLAccount number. However, now you will not be able to insert summaries. Instead, you will need to do two running totals per month--one at the group level and one at the report level.

Using the running total editor, choose {@Jan}, sum, evaluate for each record, reset on change of group (GLAccount). For the report footer, choose reset never. Repeat these two running totals for each month.

-LB

 

[sbf]

Thanks again Ibass
I have tried your proposal which gives the following output:

JAN-03 FEB-03 MAR-03 APR-03 MAY-03 JUN-03 JUL-03
(GH1) 1111
-----------------------------------------------------------------------------
1111 0 0 0 0 0 0 2
-----------------------------------------------------------------------------
(GF1) 0 0 0 0 0 0 2
-----------------------------------------------------------------------------
(GH1) 2222
-----------------------------------------------------------------------------
2222 0 0 0 0 0 0 1
-----------------------------------------------------------------------------
(GF1) 0 0 0 0 0 0 1
-----------------------------------------------------------------------------
(RF) 0 0 0 0 0 0 3
-----------------------------------------------------------------------------

Only the most recent records for July-03 seem to appear.

But based on the above report I have made seven subreports, one for each month, doing a record selection in each report:
Sub report 1, record selection:
{KLA_RATING.RATINGDATE} <dateadd(&quot;m&quot;, -6, date(year(currentdate),month(currentdate),01))
……
Sub report 7 , record selection:
{KLA_RATING.RATINGDATE} <dateadd(&quot;m&quot;, -0, date(year(currentdate),month(currentdate),01))

The seven subreports are placed across (horizontal) in the RF section of the main report and gives the right result !
Hmm.. However I will admit that this is not the most elegant solution, especially if need to do calculations across the results in each subreport.

 

[lbass]

I'm terribly sorry for steering you so wrong--my last post was off the wall. I think this will work:

From each monthly formula remove the &quot;else {@previousmo}&quot; so that the formula just returns the result for that month or else 0. Just insert a summary on the first month {@Jan} (since there is no previous value). Then instead of inserting summaries or doing running totals, create a formula for each remaining column summary like the following {@sumFeb}:

if sum({@Feb},{table.GLAcct}) = 0 then sum({@Jan},{table.GLAcct}) else sum({@Feb},{table.GLAcct})

Place these in the group footer and then suppress the details.

For summaries in the report footer, you would need to use variables for each month:

{@rptFeb} to be placed in the group footer:
whileprintingrecords;
numbervar rptFeb;
rptFeb := rptFeb + {@sumFeb};

{@displayrptFeb} to be placed in the report footer:
Whileprintingrecords;
numbervar rptFeb;

-LB

 

[sbf]

Thanks Ibass, your assistance has helped me greatly.

I did a slight modification to your formula, as the recent rating sometimes is more than one month old:
{@sumMar}:
if sum({@Mar},{table.GLAcct}) >0 then
sum ({@Mar},({table.GLAcct}) else
if sum({@Feb},{table.GLAcct})>0 then
sum({@Feb},{table.GLAcct}) else
if sum ({@Jan},{table.GLAcct}) > 0 then
sum ({@Jan},{table.GLAcct}) else
if sum ({@Dec},{{table.GLAcct}) >0 then
sum ({@Dec},{{table.GLAcct})

Your trick with variables worked out fine.
I have one minor problem; there are two records on one GlAccount in July and the monthly formula selects both rating values, however, I am able to control the user input to the by marking the non valid values.

Once again - many thanks. //kla