
Percentage calculation in tables
John Witherspoon Dec 28, 2010 9:47 PM (in response to Abhijit Bansode )I'd implement most of it in the data model. Maybe something like this:
AsOfMonth, Type, Month, Weight
201004, Monthly, 201004, 1
201004, Quarterly, 201004, 3
201004, Quarterly, 201005, 2
201004, Quarterly, 201006, 1
201004, Half Yearly, 201004, 6
201004, Half Yearly, 201005, 5
201004, Half Yearly, 201006, 4
201004, Half Yearly, 201007, 3
201004, Half Yearly, 201008, 2
201004, Half Yearly, 201009, 1
etc.AsOfMonth, Period
201004, 2H2009And then your chart would be a pivot table like this, I think:
Dimension 1 = AsOfMonth // put on left
Dimension 2 = Type // put on top
Expression = (sum(CancellationAmount * Weight)/sum(Weight))/(ARAmount/6)I'm also assuming you got the half yearly expression wrong. You're dividing by 6 and I'd think you'd want to divide by 21 to get a weighted average of the cancellation amounts. If you really wanted 6, then I'm not sure what you're doing, and the above wouldn't work as is.

Percentage calculation in tables
Abhijit Bansode Dec 29, 2010 1:20 AM (in response to John Witherspoon )thanks for reply.
do i nedd to map all YM values with each another YM value in respective time formats.
also not got what does Weight here stands for .
i'm using below formula to calculate HalfYearly Cancellation Rate
={(((Cancellation amount(1st month of half term)*6+(Cancellation amount(2nd month of half term)*5+(Cancellation amount(3rd month of half term)*4+...+(Cancellation amount(6th month of half term)*1))/21*3.5}/(ARamount of Previous term/6)
i'm dividing by 6 in Quarterly Cancellation rate calculation.

Percentage calculation in tables
John Witherspoon Dec 29, 2010 1:11 PM (in response to Abhijit Bansode )If you take this approach, yes, you map all YM values to every YM value that they relate to. It should be simple enough with script using concatenate and while loops. I can write an example if you need it.
You're using a weighted average. "Weight" is the weighting that you are using in this average. Notice how the numbers correspond to the numbers you're using, the ones you're multiplying by, and the sum of the numbers is the number you're dividing by. So I'm duplicating your calculation by storing the weight as data.
Still not sure what you're doing with your half yearly calculation, though. In the spreadsheet, you divided by 6. In your post, you divided by 21*3.5 = 73.5. I really think you want to divide by 21 to get a weighted average?

Percentage calculation in tables
Abhijit Bansode Dec 30, 2010 12:59 AM (in response to John Witherspoon )below is the sample data i'm using , plese provide me with an example :
ACC_RCV_AMT Cancellation_amount YM
3000 30 200910 3000 35 200911 3000 54 200912 3000 45 201001 3000 80 201002 3000 20 201003 3000 50 201004 3000 20 201005 3000 20 201006 3000 30 201007 3000 20 201008 6000 20 201009 2000 30 201010 2000 30 201011 2000 20 201012 2000 30 201101 2000 20 201102 2000 40 201103 2000 34 201104 2000 36 201105 2000 38 201106 2000 40 201107 2000 42 201108 2000 44 201109 2000 46 201110 2000 48 201111 3000 50 201112 3000 52 201201 3000 54 201202 3000 56 201203 3000 58 201204 3000 60 201205 3000 62 201206 3000 64 201207 2000 66 201208 2000 68 201209 2000 70 201210 2000 72 201211 2000 74 201212
i've provided only monthy and quarterly cancellation rate formulas in excel sheet.. in previous post i've given formula for half yearly calculation.
below are the actual calculations i'm doing:
Cancellation rate in 1 quarter, 2010?(((50*3)+(20*2)+(20*1))/3 months)/(18000/6 months)=2.3%Cancellation rate in 1H, 2010?(((50*6)+(20*5)+(20*4)+(30*3)+(20*2)+(20*1)) /6 months) /(18000/6 months)=3.5%Cancellation rate in 2H, 2010?(((30*6)+(30*5)+(20*4)+(30*3)+(20*2)+(40*1)) /6 months)/(21000/6months)=2.7%
Percentage calculation in tables
John Witherspoon Dec 30, 2010 6:30 PM (in response to Abhijit Bansode )Sorry to do this to you, but due to time constraints and poor understanding on my part of what you want, I'm going to have to walk away from this problem.
I originally thought you had an AR table and a cancellation table, as that's what you showed in the spreadsheet. I originally thought that you were using a rolling quarter and rolling half year for the cancellation amounts and weighting, but now I'm thinking that maybe you meant for the quarterly number to be the same for every month in the quarter, and every half yearly number to be the same for every month in the half year.
Also, looking closer at your logic for which half year to use for the accounts receivable, it doesn't cover March, and appears to produce strange results, with January and February 2010 getting 1H2010, March 2010 getting nothing, April through Septebmer getting 2H2009, and October through December getting 1H2010 again. I'm guessing that's not really what you want, and I could try to guess what you DO want, but I seem to have been pretty bad at guessing so far in this thread.
It looks doable, but I've already spent an hour or two on this, and now it looks like I'd need to throw it all away, start over, and with a significantly more complicated problem than I thought I was getting into. I just don't have the hours to devote to this to try to come up with a clean solution, and I have no confidence that even if I did so, it would be at all what you were really asking for, because I seem to have been all wrong about what you were asking for so far.
Anyone else want a crack at this one?

Percentage calculation in tables
Abhijit Bansode Dec 31, 2010 1:11 AM (in response to John Witherspoon )thanks for giving your valuable time.
let me explain , here 1st half of year is from april to sept and second half is from oct to next year's march.
so when i talk about cancellation rate in 1Half 2010 then , this will be from months between april 2010 to september 2010 .
and its previous term is form oct 2009 to march 2010.
anyone have any idea for this?




