Iteration logic question

[danny118088]

Dear Experts...

I need help on producing monthly aging report with below logic/step:
1. I'm retrieving list of material movement with its value to get Total Inventory Balance for each material
2. Remove all negative values then sort material movement based on posting date
3. Based on the total from step one, we want to back into the total by using the most recent positive values. When we get to the total, we change the value of the oldest posted value, if needed, so that the totals match up with the total from step 1.
4. Report the aging values for the material based on the data from step 3.

I'm having problem with step 3 where it seems that we need to do some iteration logic. Any input on how to do this?
I'm using BI Query as source so stored procedure may not work.

Thank you in advance for the help.

cheers,
D

 

[danny118088]

Additional info that I'm using Crystal XI.

 

[GJParker]

It would be easier to understand your problem if you could supply some sample data and expexcted results

Gary Parker
MIS Data Analyst
Manchester, England

 

[Madawc]

Crystal reports only read the data once. You might be able to get round this using a subreport and returning a value. Maybe a subreport in the report header that gets the "oldest posted value".

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[danny118088]

Hi all...

I put some sample data below:

Table 1
Material Value Posting Date
A 1000 10/1/2008
A -50 11/20/2008
A -500 11/25/2008
A 200 12/10/2008
A 50 2/20/2009
A 200 3/1/2009
A -500 3/14/2009
A 300 4/1/2009
Total 700

Table 2
Material Value Posting Period
A 150 Dec
A 50 Feb
A 200 Mar
A 300 April
Total 700

Note: Dec posting value is reduced from 200 to 150 in order to match up Total between Table 1 dan 2. (=700)

Table 3 (final layout format)
Report run end of April
Material 0-1 Months 1-2 Months 2-3 Months
A 300 250 150
…
…

Any input is appreciated.

Thanks,
D

 

[lbass]

I'm unclear how you determined to use 12/2008 as the earliest posting date, when 10/2008 looks more likely.

Try something like this. Do not remove negative amounts.

Create a formula {@pos}:

if {table.amt} > 0 then {table.amt}

Then create conditional formulas like this:

//{@0to1mos}:
if {table.date} in dateadd("m",-1,{tble.date}) to currentdate then {@pos}

//{@1to2mos}:
if {table.date} in dateadd("m",-2,{tble.date})-1 to dateadd("m",-1,{tble.date})-1 then {@pos}

//etc.

You would insert sums on these at the material group level and then suppress the detail section.

//{@lastval} (assuming a group on material) for the group footer:
sum({table.amt},{table.material})- sum({@0to1mos},{table.material})-sum({@1to2mos},{table.material})

The trick is to figure out the earliest period, and the number of formulas needed between that and the currentdate. Not sure what your logic would be for that.

-LB