Solvent Ratio

[ErnstNoto]

Hi

I have a solvent ratio report that sees every month separetly

Nbr of delivered invoices each month
Amount paid on these etc….
Presented like this:

2013
Month - Nbr_Inv - Recevied_Pricipal - Current_Pricipal - Paid_Principal - SolventRatio
Janury - 428 - 500 - 200 - 300 - 60%
January - 500 - 600 - 350 - 250 - 41,6%

etc.....

The solvent% here is a calculation within each month.
The sums are all connected to the invoices delivered in each month.
Now there is also a wish to get a new figure in, a acumulated solvent ratio
Month 1 will be as is
Month 2 will be calculated of the sums of month 1 og 2 etc…

Ex:
Month 1: 428 invoices - Principal: 500 – Paid 300 – Solvent = 60% - accumulated = 60%
Month 1: 500 invoices – Principal: 600 - Paid 250 – Solvent = 41,6 - accumulated = 30%

Added as a New column:

Month - Nbr_Inv - Recevied_Pricipal - Current_Pricipal - Paid_Principal - SolventRatio - AccSolvent
Janury - 428 - 500 - 200 - 300 - 60% - 60%
January - 500 - 600 - 350 - 250 - 41,6% - 50%

The calculation in itself is not the issue. The problem is to get Crystal to do as I wish. I have tried several things.
I have tried making separate running totals and trying to make separate formulas to calculate each month.
A running total for month 1 yr 1 + Month 2 Yr 1 etc… and then using these numbers to make a solvent ratio for each month ahead, each formual adding a new month. But this does not work. Each running total becoms 0 when used in a formula not connected to its current mont/group
Anyone have an idea on how to make this work, and if there is a way of doing this without making a formula for each month but a function/formula that can work by itself and calculate a accumulated solvent ratio
hope I managed to provide a comprehensible explanation on what I am trying to acomplish


Best Regards
Ernst Noto

 

[ErnstNoto]

a type error in one of my examples - accumulated solvent is in month 2 offcourse 50% (not 30)

Best Regards
Ernst Noto

 

[Charliy]

Sounds like you need two sets of Running Totals. One that resets on change of group, and one that does not reset.

 

[ErnstNoto]

Thank you! I might have done something wrong but as I explained above I have already tried this and it dont work. When using a running total for month 1 in the formula for month 2, it still resets. It comes out as zero in formula for month 2. I'll try again in case I have done something wrong. But this way will also mean huge amount of formulas, is there not an easier way?

Best Regards
Ernst Noto

 

[Charliy]

If you need you need to see a previous group's total - for instance if February needs to see the total of January - then you'll need to use a shared variable.

Create a second footer for the group, and in it place a formula like this:
SHARED NUMBERVAR PREVTOT := {#group total}

Then if you need to reference it in the next group, you formula will look like:
SHARED NUMBERVAR PREVTOT;
{#group total} - PREVTOT <this would give you the difference between the total for two month (or regions or whatever your group is>

 

[ErnstNoto]

Hi! Feeling a bit dumb here now cause I dont get it?

I have f.ex these numbers using a running total on a formula called {@02 Dir Paid}

Jan - {#02 Dir Paid Mth} = 660 148
Feb - {#02 Dir Paid Mth} = 0
Mar - {#02 Dir Paid Mth} = 3 654 927
Apr - {#02 Dir Paid Mth} = 61 137
May - {#02 Dir Paid Mth} = 699 702
Jun - {#02 Dir Paid Mth} = 77 000
Jul - {#02 Dir Paid Mth} = 0

If I understood correctly I should then create a formula like: SHARED NUMBERVAR PREVTOT:= {#02 Dir Paid Mth}

(This gives me the same amount as the Group total in each Group?? Gues I am doing something wrong)

Then to use this in the next Group, I should make anotherformula like

SHARED NUMBERVAR PREVTOT; {#02 Dir Paid Mth} - PREVTOT (This just always gives me 0 in every Group.

What am I doing wrong. The idea I Guess was that the first formula is suppose to give med last months sum?


Like

Jan - {#02 Dir Paid Mth} = 660 148 then a shared variabel = 0 in the first month
Feb - {#02 Dir Paid Mth} = 0 then a shared variabel = 660 148 in the second month
Mar - {#02 Dir Paid Mth} = 3 654 927 then a shared variabel = 0 in the third month
Apr - {#02 Dir Paid Mth} = 61 137 then a shared variabel = 3 654 927 in the forth month
May - {#02 Dir Paid Mth} = 699 702 then a shared variabel = 61 137 in the fith month
Jun - {#02 Dir Paid Mth} = 77 000 then a shared variabel = 699 702 in the sixth month
Jul - {#02 Dir Paid Mth} = 0 then a shared variabel = 77 000 in the seventh month

Is this the idea? If so I cant get it to work. What am I doing wrong?



Best Regards
Ernst Noto

 

[Charliy]

Did you use a second Group Footer (which can be suppressed) for the first formula?
If you put that first formula in the same band where you're trying to us it, it will contain the current month's results instead of the previous month's.

 

[ErnstNoto]

yes I placed formula 1 in a seapare Group footer (GF2b). I have however been moving it around to se the effects, and formula 1 (SHARED NUMBERVAR PREVTOT:= {#02 Dir Paid Mth}) gives the same result in both Group footers (GF2A&GF2B). Formula 2 (SHARED NUMBERVAR PREVTOT; {#02 Dir Paid Mth} - PREVTOT ) however, will be 0 if placed in GF2B. This happens wether formula 1 is in GF2a or GF2b. But if I Place formula 2 in GF2A, then it will give the same result as formula 1.

What am I doing doing wrong

Best Regards
Ernst Noto