Rolling Dates\Qty

[postmanplod]

Hello,

I am unable to work the following out:

From a Part Transaction table, (connected via XML), we have Part Qty and Part Date. I have been asked to calculate the total of the qty for the past six months, based on today's date - this will obviously change every day. I also need to calculate the total qty for the previous six months prior.

So, to summarise I require :

A rolling sum of the qty for the past 6 months

A rolling sum of the qty for previous six monts prior to the above

Basically the result should change day to day as the date range's are based on the current date.

I have been working with running totals and the date serial function but so far have not produced the desired outcome.

Thank you in advance for any help received.

Michael

 

[lbass]

{table.partdate} > dateadd("m",-12,currentdate)

Then use formulas like this:

if {table.partdate} > dateadd("m",-6,currentdate) then
"Last Six Months" else
if {table.partdate} in dateadd("m",-12,currentdate) to dateadd("m",-6,currentdate) then
"Seven to Twelve Months ago"

Insert a group on this formula and then insert sums on the qty field at the group level.

-LB

 

[postmanplod]

Thanks but this means I'm receiving group totals at every level. I just require two figures on the report - one showing previous six months and the other showing the prior six months.

Michael

 

[postmanplod]

I have a running sum (Part Qty) with the following in:

{PartDate} between DateAdd(M,-12,CurrentDate()) and CurrentDate()

But keep receiving a message saying the remaining text is not part of the formula.

 

[lbass]

You haven't explained what you mean "for every level." Why are you using a running sum? You can't use 'between/and' in Crystal syntax.

Do you mean you want to watch the total accumululate? Then you would use a running total with evaluation formulas like:

{table.partdate} > dateadd("m",-6,currentdate) //1st rt

Or for the 2nd rt:

{table.partdate} in dateadd("m",-12,currentdate) to dateadd("m",-6,currentdate)

If you only need the totals, then you could write formulas like this:

//{@lastsixmonths}:
if {table.partdate} > dateadd("m",-6,currentdate) then
{table.qty}

//{@seventotwelvemonthsago):
if {table.partdate} in dateadd("m",-12,currentdate) to dateadd("m",-6,currentdate) then
{table.qty}

Place these in the detail section and then insert summaries at whatever level you wish--group and/or grand total.

-LB

 

[postmanplod]

Yes, this is it!! I only needed the totals, thank you for your help.

Michael