Skip to Content
author's profile photo Former Member
Former Member

Budget with months

Hi,

I am trying to set up a CR that will pull information for a certain month. I am trying to set it up so it will automaticly pull last year's stats (from the same month the year before) and then I'd set up a variance column.

I cannot seem to get it set up right. I'm trying to set up a cross-tab with as much flexablity as possible.

Thank you in advance for any assistance you can offer.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 27, 2008 at 04:50 PM

    Justin,

    What you are trying to do shouldn't be too tough. The actual "Hot To Do It" really depends on how you've got things setup with you parameters.

    I hate making assumptions, but I'll assume that you are using a single date parameter that accepts range values (you put both begin and end dates in the same parameter prompt).

    1. Open you Select Expert dialog box.

    2. Click the "Show Formula >>>" button

    3. If you have more than one selection criteria, find the portion that pertains to the date selection.

    It should look something like:

    {Table.DateField} = {?ParameterName}
    

    4. Add the following code to it:

    OR ({Table.DateField} >= DATEADD('yyyy', -1, MINIMUM({?ParameterName})  AND 
    {Table.DateField} <= DATEADD('yyyy', -1, MAXIMUM({?ParameterName}))
    

    Hope this helps,

    Jason

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 27, 2008 at 10:41 PM

    Thank you! It worked.

    It may just be the fact that I had a long day at work, or I just stink at this. I am trying to get the totals to show the difference, but I can't seem to change the summery. Any idea?

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Justin,

      Here's what you want to do.... It involves the creation of a few new formulas.

      1) CurrentRange = IF {tbl.Dates} = {?DateRange} THEN {tbl.FieldYouWantToTotal}
      
      2)PreviousRange = IF {tbl.Dates} != {?DateRange} THEN {tbl.FieldYouWantToTotal}
      
      3) CurrectTotals = SUM({@CurrentRange})
      
      4) PreviousRange = SUM({@PreviousRange})
      
      5) Difference = {@CurrentTotals} - {@PreviousTotals})
      

      I think this should do the trick. 😊

      Jason

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.