6 Replies Latest reply: Dec 31, 2010 1:11 AM by Abhijit Bansode RSS

    Percentage calculation in tables

    Abhijit Bansode

      Hi ,

      I've an requirement of calculation of percentage base on amount of previous tem.

      attached sheet contains detail requirements and the calculation need to be done ..

      plese guide me , how to acheive the same in QlikView and what is the feasible approach of doing so.. means do i implement it at modelling level or report level?

        • Percentage calculation in tables
          John Witherspoon

          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, 2H2009

          And 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

              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 Half-Yearly 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

                  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

                      below is the sample data i'm using , plese provide me with an example :

                       

                      ACC_RCV_AMTCancellation_amount
                      YM


                       

                      300030200910
                      300035200911
                      300054200912
                      300045201001
                      300080201002
                      300020201003
                      300050201004
                      300020201005
                      300020201006
                      300030201007
                      300020201008
                      600020201009
                      200030201010
                      200030201011
                      200020201012
                      200030201101
                      200020201102
                      200040201103
                      200034201104
                      200036201105
                      200038201106
                      200040201107
                      200042201108
                      200044201109
                      200046201110
                      200048201111
                      300050201112
                      300052201201
                      300054201202
                      300056201203
                      300058201204
                      300060201205
                      300062201206
                      300064201207
                      200066201208
                      200068201209
                      200070201210
                      200072201211
                      200074201212


                      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

                          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

                              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?