data for previous months

[kalpsv]

I have to create a report in which one col is to sum
the total from Ist april to previous month (i.e. the month
before the current month)

this is my formula
//Current Year's April to End of Last Month

if month({PPFTXN.VOUCHDT}-1) in [5,7,10,12] then
if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT}),4,1) and
{PPFTXN.VOUCHDT} <= date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),31) then
{@subs}
else
{@SUBS}; //@subs is nothing but cheking for null//
if month({PPFTXN.VOUCHDT}) in [6,9,11] then
if {PPFTXN.VOUCHDT} >= date(year({PPFTXN.VOUCHDT}),4,1) and
{PPFTXN.VOUCHDT} <= date (year({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}-1),30) then
{@subs}

else
IF ({PPFTXN.VOUCHDT}) in (date(YEAR({PPFTXN.VOUCHDT}),04,01) to
date(YEAR({PPFTXN.VOUCHDT}),04,30)) THEN 0

This formula works well but it is not giving the total
upto previous month and moving only the previous month
figures instead of cumulating.

I hope i am clear.
bye

 

[Naith]

Kal,

Which field is supposed to be being accumulated? It looks like it's {@subs}, but I'm aware you said that {@subs} is a null checker.

If it is {@subs} which is supposed to be added up, then assign the result of the formula to an accumulative variable.

Let's say the formula you posted is called @Form1, then I guess you would have another formula which went:

whileprintingrecords;
numbervar addme := addme + {@Form1}

Remember to reset addme when necessary.

Naith

 

[kalpsv]

To Naith

I have made use of your formula. Created three formulas
one in header one in detail section and one in group footer
i.e. grouping the data for the month.
But I am unable to get accumulated value from the month of April up to previous month.
If I try to pull the group footer value it shows zero in
either grand total section or summary section
What do you mean reset add me

my formulas are

// in group header section
while printing records;
numbervar subs;
subs = 0;
//in detail section
while printing records;
numbervar subs;
subs = //here i am checking for dates from 1.4. to previous month end for ppftxn.subscription and storing in a formula called @opbal//

//in group footer section
while pringing records;
numbervar subs;

in group footer section it adds all the details of that particular month and accumalates it but i am unable to get a grand total or summary total
Can you help me

bye for now

 

[Ngolem]

Kalpsv - How is this report set up?? I would think that you would be eliminating non-relevent data through the record select. then it would be a simple summing process.

Also...is it just the month of april that you want summed or all data dating back to April...or does the StartDate and StopDate vary with the report?

My suggestion is to use a startDate/EndDate parameter set and restrict the data entering the report including the following in the record selection formula

{PPFTXN.VOUCHDT}) >= {?StartDate} and
{PPFTXN.VOUCHDT}) <= {?EndDate}

Now you can probably more easily proceed with your summing formulas...which I don't really understand so far.

Jim Broadbent

 

[Naith]

Kal,

In the formula that you have in the group header, change it so that it looks like this:

whileprintingrecords;
numbervar subs_grand_total;
numbervar subs;

subs_grand_total := subs_grand_total + subs;
subs := 0;

Then, in the report footer, use a formula like your group footer formula, but instead of calling subs, call the grand total accumulator:

whileprintingrecords;
numbervar subs_grand_total;

Alright?

Naith

 

[kalpsv]

To Naith

Your formula worked fine. thank you.

Right from the beginning your formulas have been working find for me.
But now I have a small problem.
This is one formula created by me as prevmthsub i.e. to calculate the @subs figure from 1.4.2002 to previous month.

IF ({PPFTXN.VOUCHDT}) in (date(2002,04,01) to
date(YEAR({PPFTXN.VOUCHDT}),month(maximum({PPFTXN.VOUCHDT}))-1,30)) THEN
({@subs});

This works fine if there is not an entry on 31st of the month. If there be data for 31st it is not picking up.
I tried day(field) also but it gives an error message
saying the days must be betwen 1 and the days of themonth.
Can you help me?
thank you in advance.
bye

 

[synapsevampire]

date(YEAR({PPFTXN.VOUCHDT}),month(maximum({PPFTXN.VOUCHDT}))-1,30))

might be:

date(YEAR({PPFTXN.VOUCHDT}),month(maximum({PPFTXN.VOUCHDT})),1)) -1

Note that by not subtracting a month you solve 2 problems:

-In your formula, if the month is January (1), you'll get a month of zero (bad things happen...)
-By just hardcoding the first day of the next month to be used and subtracting one day will always have the last day of the month (even February).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kalpsv]

to synapsevampire

thanks for your formula. IT worked fine for the months
when data was for 31st of the month also.

But when year changed to the next year it started giving
different figures.
i.e. when i keyed in jan 2003 as the month of report
then, the data upto the previous month was not getting
updated. instead I was getting the value of last year
i.e. 1.4.2001 to 31.3.2002 in the previous month column
Also the column where I had an annual accumulation from
1.4.2001 to 31.3.2002 behaved erratically.

can you help me.
this is the formula used for annual accumulation in one column of the report

IF ({PPFTXN.VOUCHDT}) in (date(YEAR({PPFTXN.VOUCHDT})-1,04,01) to
date(YEAR({PPFTXN.VOUCHDT}),03,31)) THEN ({ppftxn.debits})


in the previous month column i used only your formula.
can you help me
thank you in advance.

 

[synapsevampire]

If you want data from the previous 4/1/<year prior to the current or use the current year if the current date is beyond 4/1/<currentyear>:

Limit the rows returned at the record selection level, not the column level, that way you can use conventional sums, etc., to do the work.

Create a formula called 4_1_Date:

// Obtain the 4_1_date to use
if dateadd('m',-1,currentdate) > date(year(@EndingDate),4,1) then
date(year(currentdate),4,1)
else
date(year(currentdate)-1,4,1)

Create a formula called Last_Month_Date:

// Obtain the Last_Month_date to use
date(year(dateadd('m',-1,currentdate)),monthdateadd('m',-1,currentdate),1)-1

In the Record Selection Criteria place:
({PPFTXN.VOUCHDT} in @4_1_Date to @Last_Month_Date

Now the report will date limit the rows returned to the report appropriately.

I could have done all of this within the record selection criteria, but the pass through SQL might fail as a result (if you use variables, you're almost assuring failure of the pass through SQL and thus degrading performance).

Plus, keeping them as separate variables will likely simplify maintenance.

(I may have some lil errors in here as I can't test right now)

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kalpsv]

synapsevampire

your formula did not work

this is the formula i am using from 1.4. to end of previous month
in my cr i.e. cr with vb5 does not support dateadd
or &quot;m&quot;
IF ({PPFTXN.VOUCHDT}) in date(year({ppftxn.vouchdt}),04,01) to
date(YEAR({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}),01)-1 THEN
({@debits})

else if month({PPFTXN.VOUCHDT}) in [1,2,3] then
IF ({PPFTXN.VOUCHDT}) in date(year(maximum({ppftxn.vouchdt})-1),04,01) to
date(YEAR({PPFTXN.VOUCHDT}),month({PPFTXN.VOUCHDT}),01) THEN
({@debits});
thank you in advance

 

[Naith]

Kal,

&quot;Your formula did not work&quot; is not very helpful. Explain what it is that you're experiencing using the formula, compared to what it is you expect.

Looking at your formula, all I would suggest is that you put in some 'Else' clauses in your If/Then statement to catch records that don't meet the condition stipulated by 'Then'.

Naith

 

[kalpsv]

to synapsempire
I tried the formulas given below :
these two formulas return the dates correctly.
but in the record selection criteria i am unable to
retrive the date pertaining to ppftxn.subcription
it only gives 0.
// Obtain the 4_1_date to use
if dateadd('m',-1,currentdate) > date(year(@EndingDate),4,1) then
date(year(currentdate),4,1)
else
date(year(currentdate)-1,4,1)

Create a formula called Last_Month_Date:

// Obtain the Last_Month_date to use
date(year(dateadd('m',-1,currentdate)),monthdateadd('m',-1,currentdate),1)-1

In the Record Selection Criteria place:
({PPFTXN.VOUCHDT} in @4_1_Date to @Last_Month_Date

instead of using current date i used my field ppftxn.vouchdt

 

[kalpsv]

to synapsevampire
I tried the formulas given below :
these two formulas return the dates correctly.
but in the record selection criteria i was able to
retrive the data pertaining to ppftxn.subcription

// Obtain the 4_1_date to use
if dateadd('m',-1,currentdate) > date(year(@EndingDate),4,1) then
date(year(currentdate),4,1)
else
date(year(currentdate)-1,4,1)

Create a formula called Last_Month_Date:

// Obtain the Last_Month_date to use
date(year(dateadd('m',-1,currentdate)),monthdateadd('m',-1,currentdate),1)-1

In the Record Selection Criteria place:
({PPFTXN.VOUCHDT} in @4_1_Date to @Last_Month_Date

i am already using a running total formula i. pageheader
detail and pagefooter as per your suggestion only.
Now in the above record selection criteria the records retrieved does not get summed up.
please help me
thank you in advance

 

[kalpsv]

to synapsevampire
I tried the formulas given below :
these two formulas return the dates correctly.
in the record selection criteria also i was able to
retrive the data pertaining to ppftxn.subcription

// Obtain the 4_1_date to use
if dateadd('m',-1,currentdate) > date(year(@EndingDate),4,1) then
date(year(currentdate),4,1)
else
date(year(currentdate)-1,4,1)

Create a formula called Last_Month_Date:

// Obtain the Last_Month_date to use
date(year(dateadd('m',-1,currentdate)),monthdateadd('m',-1,currentdate),1)-1

In the Record Selection Criteria place:
({PPFTXN.VOUCHDT} in @4_1_Date to @Last_Month_Date

i am already using a running total formula in pageheader
detail and pagefooter as per your suggestion only to sum up the data from 1.4 to end of previous month.
Now using the above record selection criteria the records retrieved does not get summed up.
please help me
thank you in advance