QTD Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query where a user chooses a period (1-12), this brings back the
correct monthly data, I also want the QTD data reported. How can one create
criteria that looks at the period that was chosen, and assigns the correct
qtr. i.e. - you select period 5 - in the Qtr field criteria I want to look
at that 5, and create Qtr criteria of something like: >=4 and <=5 to get me
the quarter-to-date data.

Essentially I just need to somehow drop in the correct criteria for qtr when
the period is chosen.
 
The way that I tackle this is to write a couple of WHERE clauses that will
extract the data needed. That will help me understand the nature of the
data entry that is needed from the user.

So, if you have a field that stores qtr as YYQQ (0401), then you know that
you need to get the year and qtr from the user.

From there, you can make the appropriate UI, and write some code to
translate it into the proper WHERE clause.
 
Assume P is the period
Quarter Start
(P-1)\3 Returns 0 to 3
* 3 returns 0,3,6,9
+1 Returns 1,4,7,10

So, to get the start of the quarter you can use the formula
(((P-1)\3) * 3) + 1
 
Back
Top