need solution

deesh · Post by **deesh** » Mon May 06, 2013 9:32 am

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

chulett · Post by **chulett** » Mon May 06, 2013 10:02 am

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?

deesh · Post by **deesh** » Mon May 06, 2013 11:16 am

it is a flat file.. and loading in to one more flat file. and sorry for the subject which i have mentioned.

chulett · Post by **chulett** » Mon May 06, 2013 11:31 am

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.

ray.wurlod · Post by **ray.wurlod** » Mon May 06, 2013 2:04 pm

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.

reachmexyz · Post by **reachmexyz** » Mon May 06, 2013 2:56 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.

kduke · Post by **kduke** » Wed May 08, 2013 9:23 pm

I would do this in a server routine.