Date Calculation to get the next quarter based on Input Date

[Titto]

Hi,

I have a requirement to calculate next quater month based on the provided month and using the derived quarter month value need to calcuate the next earliest quarter month.
For example I have a date 2005-05-20 next 4 quarters based on this dates are 08, 11,02,05 months respectively, Then i need to check the current month value (2006-03-09) so that is is '03'. as per logic i want next earliest quarter would be 05 .

Code: Select all

InDate           InMonth      Possible quarters     required qtr
2005-05-20       05            08    11   02   05     05(as it is the next)
2005-03-10       03            06    09   12   03     03(as it is equal)
2005-01-01       01            04    07   10   01     04(as 01 is past 04 is next)

Hope i explained my requirement properly,
Is there any way i can do in PX using StageVariables, any help is appreciated.

~

Code: Select all

[ArndW]

titto, I think the "next quarter" is understood when it is 3 months away from your data date. But you haven't explained

... so that is is '03'. as per logic i want next earliest quarter would be 05

what that logic is based on. Is the next quarter to be calculated from today's date? I think that the algorithm for what you want to do is going to be very simple - but it needs to be defined first.
With a current month of 3/2006 what should the values be for

DataDate
01/2006 ?
02/2006 ?
03/2006 ?
04/2006 ?
05/2006 ?
06/2006 ?
07/2006 ?

[Titto]

I am sorry, I did not explain properly,

You are right the next quarter should be calculated based on current month. But if the calculated Quarter is current month use the current month as current Quarter.

[DSguru2B]

Please restructure your question!

[deployDS]

You should define 4 stage variables as
1stQrt:
If (month(indate)+3)>12 then (month(indate)+3)-12 else (month(indate)+3)

2ndQrt:
If (1stQrt+3)>12 then (1stQrt+3)-12 else (1stQrt+3)

3rdQrt:
If (2ndQrt+3)>12 then (2nd+3)-12 else (2nd+3)

4thQrt:
If (3rdQrt+3)>12 then (3rdQrt+3)-12 else (3rdQrt+3)

CurMon:
month(currentdate())

Then, in derivation, use a multiple if statement comparing each quarter with 3 other quarters to determine which one is the closest. i.e,

If (CurMon-1st<CurMon-2nd) and (CurMon-1st<CurMon-3rd) and (CurMon-1st<CurMon-4th)
then reqdQtr=1st
else
If (CurMon-2nd<CurMon-1st) and (CurMon-2nd<CurMon-3rd) and (CurMon-2nd<CurMon-4th)
then reqdQtr=2nd
else
If (CurMon-3rd<CurMon-1st) and (CurMon-3rd<CurMon-2nd) and (CurMon-3rd<CurMon-4th)
then reqdQtr=3rd
else
reqdQtr=4th

[ray.wurlod]

For small volume of data, a BASIC Transformer stage could serve here. Use

Code: Select all

Oconv(Iconv(TheDate, "D"), "DQ")

to generate the quarter number (1, 2, 3 or 4). Then take the remainder after dividing the quarter number into the month number, added to the month number. Use the Mod() function to get the remainder after integer division.

For larger volumes use a lookuptable in the Modify stage (function lookup_int16_from_string or lookup_string_from_int16 for example) to convert the month number to a quarter number. Then proceed as above in a Transformer stage. Or build the lookup as a delimited string "1,1,1,2,2,2,3,3,3,4,4,4" in a stage variable in a Transformer stage and use the Field() function to extract the appropriate quarter based on the month number then again proceed as above.

[Titto]

Hello.

You should define 4 stage variables as
1stQrt:
If (month(indate)+3)>12 then (month(indate)+3)-12 else (month(indate)+3)

2ndQrt:
If (1stQrt+3)>12 then (1stQrt+3)-12 else (1stQrt+3)

3rdQrt:
If (2ndQrt+3)>12 then (2nd+3)-12 else (2nd+3)

4thQrt:
If (3rdQrt+3)>12 then (3rdQrt+3)-12 else (3rdQrt+3)

CurMon:
month(currentdate())

Then, in derivation, use a multiple if statement comparing each quarter with 3 other quarters to determine which one is the closest. i.e,

If (CurMon-1st<CurMon-2nd) and (CurMon-1st<CurMon-3rd) and (CurMon-1st<CurMon-4th)
then reqdQtr=1st
else
If (CurMon-2nd<CurMon-1st) and (CurMon-2nd<CurMon-3rd) and (CurMon-2nd<CurMon-4th)
then reqdQtr=2nd
else
If (CurMon-3rd<CurMon-1st) and (CurMon-3rd<CurMon-2nd) and (CurMon-3rd<CurMon-4th)
then reqdQtr=3rd
else
reqdQtr=4th

When i use the bottom portion of the logic, it is not giving proper values
for example

Code: Select all

InDate = 2005-08-10
CurrDate = 2006-03-09
CurrMon = 03
based on the first logic 
1stQtr  = 11
2ndQtr = 02
3rdQtr  = 05
4thQtr  = 08

Using the second logic to determine the next possible quarter (should be equal to the current month or next closest quarter)
 
If (CurMon-1st<CurMon-2nd) and (CurMon-1st<CurMon-3rd) and (CurMon-1st<CurMon-4th) 
       then reqdQtr=1st
else 
If (CurMon-2nd<CurMon-1st) and (CurMon-2nd<CurMon-3rd) and (CurMon-2nd<CurMon-4th) 
       then reqdQtr=2nd
else 
If (CurMon-3rd<CurMon-1st) and (CurMon-3rd<CurMon-2nd) and (CurMon-3rd<CurMon-4th) 
       then reqdQtr=3rd
else 
       reqdQtr=4th

 IF  (3-11 = -8) < (3-02 = 1)   ** which is true
 And (3-11 = -8) < (3-05 = -2)  ** which is true
 And (3-11 = -8) < (3-08 = -5)  ** which is true
  Then reqdQtr = 11                 
   But the actual value should be '05' as current month is '03' and next least number and which is greater than current month.

Any thoughts !

[deployDS]

Take the abs() of comparing values, and if u want the nearest NEXT quarter, add another condition, like :

If (CurMon-1st<0) and (abs(CurMon-1st)<abs(CurMon-2nd)) and (abs(CurMon-1st)<abs(CurMon-3rd)) and (abs(CurMon-1st)<abs(CurMon-4th))
then reqdQtr=1st
else
If (CurMon-2nd<0) and (abs(CurMon-2nd)<abs(CurMon-1st)) and (abs(CurMon-2nd)<abs(CurMon-3rd)) and (abs(CurMon-2nd)<abs(CurMon-4th))
then reqdQtr=2nd
else
If (CurMon-3rd<0) and (abs(CurMon-3rd)<abs(CurMon-1st)) and (abs(CurMon-3rd)<abs(CurMon-2nd)) and (abs(CurMon-3rd)<abs(CurMon-4th))
then reqdQtr=3rd
else
reqdQtr=4th

IF (3-11 = -8) < (3-02 = 1) ** which is true
And (3-11 = -8) < (3-05 = -2) ** which is true
And (3-11 = -8) < (3-08 = -5) ** which is true
Then reqdQtr = 11
But the actual value should be '05' as current month is '03' and next least number and which is greater than current month.[/code]


Any thoughts ![/quote]

[Titto]

No Luck !!! Still the same answer - 08 , for the example which i provided.

[deployDS]

Ok, sorry I overlooked that part... now this should work, though this doesnt seem very efficient method:

Add 4 more stage variables:
1st:
If CurMon-1stQrt>0 then (CurMon-1stQrt)*-13
else CurMon-1stQrt

2nd:
If CurMon-2ndQrt>0 then (CurMon-2ndQrt)*-13
else CurMon-2ndQrt

3rd:
If CurMon-3rdQrt>0 then (CurMon-3rdQrt)*-13
else CurMon-3rdQrt

4th:
If CurMon-4thQrt>0 then (CurMon-4thQrt)*-13
else CurMon-4thQrt

In derivation,
If (1st>2nd) and (1st>3rd) and (1st>4th)
then reqdQtr=1st
else
If (2nd>1st) and (2nd>3rd) and (2nd>4th)
then reqdQtr=2nd
else
If (3rd>1st) and (3rd>2nd) and (3rd>4th)
then reqdQtr=3rd
else
reqdQtr=4th

Letme know.

[DSguru2B]

Tito,
I took out some time to work on your problem. I came up with a routine. I tested it with all the scenarios you listed in your first post. It works. Try it out and let me know

Code: Select all

InMonth = Arg1
ActMonth = Arg2

InMonth_Qtr1 = InMonth + 3
InMonth_Qtr2 = InMonth + 6
InMonth_Qtr3 = InMonth + 9
InMonth_Qtr4 = InMonth + 12

if InMonth_Qtr1 > 12 then InMonth_Qtr1 = InMonth_Qtr1 - 12
if InMonth_Qtr2 > 12 then InMonth_Qtr2 = InMonth_Qtr2 - 12
if InMonth_Qtr3 > 12 then InMonth_Qtr3 = InMonth_Qtr3 - 12
if InMonth_Qtr4 > 12 then InMonth_Qtr4 = InMonth_Qtr4 - 12

a = ABS(ActMonth - InMonth_Qtr1)
b = ABS(ActMonth - InMonth_Qtr2)
c = ABS(ActMonth - InMonth_Qtr3)
d = ABS(ActMonth - InMonth_Qtr4)

if a < b and a < c and a < d then x = InMonth_Qtr1
if b < a and b < c and b < d then x = InMonth_Qtr2
if c < a and c < b and c < d then x = InMonth_Qtr3
if d < a and d < b and d < c then x = InMonth_Qtr4

if x < ActMonth then x = x + 3 

Ans = x

Return(Ans)

where Arg1 = Month coming in (just the month)
and Arg2 = Present Month (just the month)

if you want to pass in the entire date, thats fine too, the routine could be tweaked to handle that. But the logic is all here. Try it out and let me know

[ray.wurlod]

D'you know, that just doesn't look like a parallel routine to me! I'm fairly certain that C++ code would have more curly braces and semi-colons than appear in your code.

As noted in my earlier post, which everyone appears to have bypassed, there are solutions available that don't require either routines or stage variables.

[DSguru2B]

OOps
i did not pay attention to that. i completely forgot weather it was a server or a px job.
Sorry Tito.
If your problem doesnt get resolved using stage variables, use the logic in my routine and transform that into a px routine. it will work.
Sorry once again guyz

[kwwilliams]

As noted in my earlier post, which everyone appears to have bypassed, there are solutions available that don't require either routines or stage variables.

It appears that none of the people on the post are able to see premium content. This is all of your previous post that we can see:

For small volume of data, a BASIC Transformer stage could serve here. Use Code: Oconv(Iconv(TheDate, "D"), "DQ") to generate the quarter number (1, 2, 3 or ...

My office has agreed to pay for it, but the arrangements are coming slowly. I can't wait to get rid of the stigma of not being one of the chosen viewers of premium data.

[DSguru2B]

You have to pay to see the Premium content.