Hi,
I have to calculate gross amount based on fical year and month and my year starts from september.
Ex:Amount, Fiscal year , period, M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12
2013 5 200+300+100+599+829+700+339+09+49+83+590+489
M1 -Sep, M2-Oct, M3-Nov --, and the respective values.
Now present month is 5(May) in 2013, we have to calculate from 2012 sept to 2013 May that means 9 months, now i need to put 9 months amount in gross amount field ( M1+M2+M3+M4+M5+M6+M7+M8+M9).200+300+100+599+829+700+339+09+49
so in a amount field required M1 to M9 sum value
Please help me any one in this
need solution
Moderators: chulett, rschirm, roy
need solution
Last edited by deesh on Mon May 06, 2013 1:00 pm, edited 1 time in total.
Pretty vague requirements, I'm afraid. And "need solution" is not the best choice for a topic's subject.
Clarify for us what exactly your source is: database, flat file? If a database, do you have a date dimension that drives your fiscal v. calendar year? I'd also be curious what you've tried so far... anything?
Clarify for us what exactly your source is: database, flat file? If a database, do you have a date dimension that drives your fiscal v. calendar year? I'd also be curious what you've tried so far... anything?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Can you post a complete example of your source, something without dashes so we can see everything? Also, will your source have multiple records and you need to do this calculation once per row or would there be any kind of aggregation involved?
Really don't want to be doing a lot of guessing here and not a big fan of surprises, you need to be as specific as possible so people can help get you on the right path without a ton of going back and forth for clarifications.
Still curious what you have tried so far.
Really don't want to be doing a lot of guessing here and not a big fan of surprises, you need to be as specific as possible so people can help get you on the right path without a ton of going back and forth for clarifications.
Still curious what you have tried so far.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Is there one figure per month per row, or many rows per month, or some other format?
Basically, I'd be looking at a solution for muliple rows per month, which I'd aggregate grouped by month. I'd also calculate the most recent year and month in the data and, from that, determine the month and year of the preceding 12 months, and add these.
Basically, I'd be looking at a solution for muliple rows per month, which I'd aggregate grouped by month. I'd also calculate the most recent year and month in the data and, from that, determine the month and year of the preceding 12 months, and add these.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 296
- Joined: Sun Nov 16, 2008 7:41 pm
Try this..
You already have month ( 5) i.e period 5 in your input and your fiscal year starts from september.
If month is 5 ( not greater than 8 ), then you want to add 4 to it as ( sep, oct, nov and Dec are 4 months ) which gives 9. If 9 add M1+M2+M3+M4+M5+M6+M7+M8+M9.
If the month say August i.e 8 ( not greater than 8 ) , than add 4 to get 12. M1+M2+M3+M4+M5+M6+M7+M8+M9+M10+M11+M12.
If the month is Nov i.e 11 ( greater than 8 ), substract 8 which gives 3. Add M1+M2+M3
If month is greater then 8 then substract 8 to get the M suffix additions.
If month is less than 8, then add 4 to get the M Suffix additions.
Tedious Transformation.. You can try looping to get the M Suffixes dynamically.
You already have month ( 5) i.e period 5 in your input and your fiscal year starts from september.
If month is 5 ( not greater than 8 ), then you want to add 4 to it as ( sep, oct, nov and Dec are 4 months ) which gives 9. If 9 add M1+M2+M3+M4+M5+M6+M7+M8+M9.
If the month say August i.e 8 ( not greater than 8 ) , than add 4 to get 12. M1+M2+M3+M4+M5+M6+M7+M8+M9+M10+M11+M12.
If the month is Nov i.e 11 ( greater than 8 ), substract 8 which gives 3. Add M1+M2+M3
If month is greater then 8 then substract 8 to get the M suffix additions.
If month is less than 8, then add 4 to get the M Suffix additions.
Tedious Transformation.. You can try looping to get the M Suffixes dynamically.