Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Date in Formula Post Reply Post New Topic
Author Message
ridershaun
Newbie
Newbie


Joined: 31 Aug 2010
Online Status: Offline
Posts: 32
Quote ridershaun Replybullet Topic: Date in Formula
     Posted: 05 Mar 2015 at 6:01am
I currently have a report that you enter the month and year to run PO usage history and it looks back at the past 12 months and then gives you an average monthly usage, I now have to add to that same report a three month usage average and I'm having trouble getting it to work correctly.
 
On the report select expert the formula I'm using is listed below.
 
datediff('m',{ORDER.PO_DATE},date({?Year},{?Month Number},1)) in 0 to 11
 
In the 12 month average use formula I have 'Sum ({PO.REC_QTY}, {PO.ITEM})/12'
 
and I created another formula field for the 3 month average which is 'if datediff('m',{ORDER.PO_DATE},date({?Year},{?Month Number},1)) in 0 to 2 then Sum ({PO.REC_QTY}, {PO.ITEM})/3'
 
Though it is still giving me the 12 month total instead of the 3 month total I want.
 
Any thoughts or help would be appreciated.
 
Thank You
IP IP Logged
adavis
Senior Member
Senior Member


Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
Quote adavis Replybullet Posted: 05 Mar 2015 at 9:44am
Does the report need to return both the 12 month average and the 3 month average?

I would consider removing the 12 month formula from the select expert and creating two different formulas, one for the 12 month and one for the 3 month. Place each of these in the report.

You could then conditionally suppress one or neither of the fields depending on what the user needs to see.

Edited by adavis - 05 Mar 2015 at 9:46am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Mar 2015 at 4:43am
one way,
alter your formula to zero out the values that want to exclude from  the 3 month total
//3monthsum
if datediff('m',{ORDER.PO_DATE},date({?Year},{?Month Number},1)) in 0 to 2 then {PO.REC_QTY}
use this the same way you did for your 12 month calculation
'Sum ({@3monthsum}, {PO.ITEM})/3'


Edited by DBlank - 09 Mar 2015 at 4:44am
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.