Comparing prev records

Suja

Hi All
I have a problem to tackle with ....Pls help with your inputs.
I need to search through the customer records and see if they have bought any thing in 2007 and 2008 and if have more than 1 transaction in a year, then have to record it to plot the customer retention graph...any idea how to go about this

thanks in advance

DaveT

Hi Suja,

Do a report off the customer table and in the OnAfterGetRecord count the number of invoice (I'm guessing this is the indicator needed).
Declaring a record variable for the Sales Invoice Header table, filter down to year and customer and test the COUNT

Hope this helps

Suja

Hi Dave
thanx for the reply....pls see to the updates

I managed to get the Indicator / count to know whtr they have entries in Yr-2007(as 2) or Yr-2008(as 4). BUt I need to compare this value ,for each customer and have to flag retention - if customer have bought from us in both the yrs or attrition if the customer have entry in 2007 but not in 2008 etc... I want to perform this calculation after i go through all the Posted records for 1 customer and have to set some flag - yes/no... and then have to repeat the same for other customers....So where to put these comparisons...

Thanks again
suja

DaveT

Hi Suja,

You have the hard work done. Add in the code after your code getting the count (assuming you use an integer to store the counts).

e.g.

If YR2008 > 1 then
  begin
    if YR2007 > 1 then
      IndicatorText := 'Retained'
   else
      IndicatorText := 'New Customer';   // Use CURRREPORT.SKIP; to skip new customers
  end
else
  begin
  end  if YR2007 > 1 then
      IndicatorText := 'Lost'
   else
      IndicatorText := 'Dormant';   // Use CURRREPORT.SKIP; to skip customers with no sales in the two year

IndicationText is just a text variable to print

Suja

Hi Dave
I have achieved as below, still dilemma on how to get the flag values..Pls advice...

SalesInvoice Header [Header]
No. Order Date 2005 2006 2007 2008

[SalesInvoice Header- Group Header By Customer No.]
Customer No. -- C0001649 SONY ELECTRONICS

[SalesInvoice Header- Body]

PSINV-10311487 04/12/07 No No Yes No
PSINV-10311488 04/02/07 No No Yes No
PSINV-10311488 04/03/07 No No Yes No
PSINV-10311488 04/08/07 No No Yes No
PSINV-10311488 04/08/08 No No Yes Yes
PSINV-10311488 04/09/08 No No Yes Yes

[Group Footer By Customer] - FLAGSResult For Customer No: C0001649 Attrition : (yes/no) Retention(yes/no) Acquisition: (yes/no)

Only after I get all the records for the customer I can calcualte attrition/acquisition/retention.Here to get those values I need to compare all the records of the customer. If Cust A have bought in 08 and 07 then retention - yes, if Cust A have more than 1 entry in 07 also retention- yes etc....etc....If only got values in 08 then acquisition - yes etc...etc

I am upto here, but didnt managed to get the values of the FLAGS yet...Where to place the code to get these grouped by customer. I put under Group footer but not working...

DaveT

Hi Suja,

Without the code I'm just guessing. Put the code in the OnPreSection of the Group Footer of the customer and this should work

Suja

HI Dave
Here comes a bunch of thanks to you for sharing the ideas with me, and it did worked.. Thanks a lot =D>

I managed to get the Flag values (whether attrition/ retention/ acquisition) by putting the code as following in the report - Sales Invoice header -Group header - presection, as per suggested.



IF "2008ValInter">=1 THEN
BEGIN
IF "2007valInter" = 0 THEN
AcquisVal := TRUE
ELSE IF "2007valInter" >=1 THEN RetenVal := TRUE
END

ELSE
IF "2008ValInter"=0 THEN
BEGIN
IF "2007valInter" >1 THEN
RetenVal := TRUE
ELSE IF "2007valInter" = 1 THEN AttriVal := TRUE;
END;

The value came out correctly by customers by comparing there records for 2007/ 2008

If i want to get the whole total of how much customer's (company level) are under attrition/retention/ acquisition which way to follow ?

DaveT

Great Work! \:D/

Glad to Help

Suja

Hi Dave

If i want to get the whole total count of how much customer's (company level) are under attrition/retention/ acquisition which way to follow

( I am getting these flag values by customers using the coding which put under the Sales Inv Header - Group footer- presection. The report is buld on Sales Invoice Header)

Pls advice

DaveT

Hi Suja,

All you need to do is create integer variables for each type and increment in the OnPreSection of the groupfooter e.g.

if RetenVal then
"No. of Retentions" += 1; // Same as "No. of Retentions" := "No. of Retentions" + 1;

Suja

Hi Dave
I had tried that and I got the value correctly by customer. But what i want is the Grand Total of how many attritions/ retentions/ acquisitions we have and to display it at the end of the report So where to place the variable and in which section to display.
The code i put on the Presection of the Group Header of Sales Invoice Header as below

IF AttriVal = TRUE THEN AttriValTOT := AttriValTOT +1;
IF RetenVal = TRUE THEN RetenValTOT := RetenValTOT +1;
IF AcquisVal = TRUE THEN AcquisValTOT := AcquisValTOT +1;

Thanks

DaveT

Hi Suja,

Then all you need to do is add a Footer section and print the values of AttriValTOT, RetenValTOT and AcquisValTOT.

Suja

Hi Dave
I tried to put the Footer section of Sales Inv Header, but value displayed there as 0. Calcualtion of the variable are put under the Grp Footer - Presection...MY report now coming as follows

Customer details 2007 2008 Acquisition Retention Attrition

Group Footer - Sales Inv Header C0102167 1 0 No No Yes

Group Footer - Sales Inv Header C0132167 1 0 No No Yes
Group Footer - Sales Inv Header C0172167 0 2 Yes No No

((((Calculation added inside the Group footer presection to calculate total, but no value is coming out.))))
Footer of Sales Inv Header GRAND TOTAL 0 0 0

required output 1 0 2

DaveT

Hi Suja,

Can you check -

Are these local or global variables?
Are you reseting the values ?

Try following the code in the debugger

Suja

Hi dave
I declare them as Global Variables.
No, Not resetting the values at all..
Try following the code in the debugger---u mean any sample codes ???