none
Year to Date Calculations RRS feed

  • Question

  • I have a report where I have to display a year to date result. Here is the problem I have, the report has a parameter set to call in a particular report month. There I want it to display the numerator, denominator and that monthly rate - all which is fine. But what it also needs to do is to give me the year to date rate based on the previous month's numerator and denominators. Can I do that if my query is being prompted to one month only - can it still calculate on the previous months?  I am fine doing it from a query or report - wherever it works, if it works.

    Appreciate any help on this!

    TJ

    Wednesday, October 12, 2016 5:52 PM

Answers

  • Is it that the numerator is the month number, the denominator is the year number? October 2016 would therefore have the two numbers 10 and 2016? To get all the previous number pairs, you would query your table like this

    SELECT YourTable.MonthNum, YourTable.YearNum, YourTable.whatever
    FROM YourTable
    WHERE (((YourTable.MonthNum)=thisMonth-1) AND ((YourTable.YearNum)=2016));
    


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 12, 2016 6:31 PM

All replies

  • Hi TJ,

    Without seeing what you got, I will take a guess it's possible because let's say the user enters 10 (for October), then all you have to do to get September's numbers is to subtract 1 to it, right?

    Just thinking out loud...

    Wednesday, October 12, 2016 5:57 PM
  • For each month, we enter in a number in two fields (numerator and denominator).  In this report (query) - when I pull in the month of October - I need it to look at the numerator and denominators for all the previous months for each record so that I can display the year to date results.  Does that make sense?
    Wednesday, October 12, 2016 6:19 PM
  • Is it that the numerator is the month number, the denominator is the year number? October 2016 would therefore have the two numbers 10 and 2016? To get all the previous number pairs, you would query your table like this

    SELECT YourTable.MonthNum, YourTable.YearNum, YourTable.whatever
    FROM YourTable
    WHERE (((YourTable.MonthNum)=thisMonth-1) AND ((YourTable.YearNum)=2016));
    


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, October 12, 2016 6:31 PM
  • Perhaps it would be helpful to post the SQL of your query here so we'll have a better understanding of what your query is doing now and how to change it to do what you want.

    -Bruce


    • Edited by Bruce Hulsey Wednesday, October 12, 2016 6:41 PM
    Wednesday, October 12, 2016 6:40 PM