Page 1 of 2
Date Calculation to get the next quarter based on Input Date
Posted: Thu Mar 09, 2006 9:06 am
by 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.
~
Posted: Thu Mar 09, 2006 9:18 am
by 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 ?
Posted: Thu Mar 09, 2006 9:40 am
by 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.
Posted: Thu Mar 09, 2006 9:42 am
by DSguru2B
Please restructure your question!
Re: Date Calculation to get the next quarter based on Input
Posted: Thu Mar 09, 2006 9:50 am
by 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
Posted: Thu Mar 09, 2006 11:34 am
by ray.wurlod
For small volume of data, a BASIC Transformer stage could serve here. Use
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.
Re: Date Calculation to get the next quarter based on Input
Posted: Thu Mar 09, 2006 3:22 pm
by Titto
Hello.
deployDS wrote: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 !
Re: Date Calculation to get the next quarter based on Input
Posted: Fri Mar 10, 2006 10:04 am
by 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]
Posted: Fri Mar 10, 2006 10:30 am
by Titto
No Luck !!!
Still the same answer - 08 , for the example which i provided.
Posted: Fri Mar 10, 2006 11:02 am
by 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.
Posted: Fri Mar 10, 2006 11:41 am
by 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
Posted: Fri Mar 10, 2006 11:48 am
by 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.
Posted: Fri Mar 10, 2006 11:52 am
by 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
Posted: Fri Mar 10, 2006 3:50 pm
by kwwilliams
ray.wurlod wrote:
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.
Posted: Fri Mar 10, 2006 4:11 pm
by DSguru2B
You have to pay to see the Premium content.