Summing Current & Prior Period Values

[jbull13]

I am building a report with Current Month and Prior Month summaries in seperate columns. My source "fact" table has MONTH_ID and I have a look-up table with the PRIORMONTH_ID.

I've looked at SUM and LASTFULLMONTH but I'm puzzeled about the syntax.

My question is: What should the formula look like to sum the data for PRIORMONTH?

The result needs to look something like

Month Current Month Sales Prior Month Sales
Jan03 1234 3456
etc.
etc.


Thanks,

John

 

[synapsevampire]

This is dependent upon the version of Crystal and the database, you'll get more accurate, tailored answers if you post basic environment info.

You can use a Running Total and place criteria in the evaluate->Use a Formula

Or if you can live with a slightly different format, you can create a Cross-Tab, and group by the date setting the options to data will be grouped by month.

-k

 

[jbull13]

I'm using CR v9 and the data is in Access.


Thanks,

John

 

[jbull13]

Synapsevampire:

Can you give me an example of what the formula in the running total evalutation section would be. I'm still lost about how to qualify the sales field to sum for the prior period.

Pseudo code example:

Current sales = sales(timeperiod, JAN03)
Last month sales = sales (timeperiod, LOOKUP.TIMETABLE(PriorPeriod, JAN03))

Last month sales evaluates to Sales(time period, DEC02)
In this way, I don't have to hard code the time reference and can just refer to the lookup table.

So far I have not been able to create this ideas in Crystal, but I'm a novice. I hope one of experts can help.

Thanks,

John

 

[synapsevampire]

It's a shame if this is kept in a non date format, because the evaluate->Use a formula for the previous month would be:

{table.date} = lastfullmonth

So if you have a code to represent a time period (i.e., DEC03) instead of meaningful data, you'd have to code this out (you pay the price for your architects weak attempt at being clever):

If left({table.period},3 = "JAN" then
"DEC"+picture(totext(val(mid({table.period},3,2))-1,0,"&quot,"00&quot
else
If left({table.period},3 = "FEB" then
"JAN"+picture(totext(val(mid({table.period},3,2)),0,"&quot,"00&quot
...etc...

The above syntax may need some massaging as I can't test using CR right now.

Hopefully you do have a date field and can avoid this.

-k