topic Percentage calculation in tables in QlikView

Percentage calculation in tables

AbhijitBansode — Tue, 28 Dec 2010 07:50:25 GMT

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

johnw — Wed, 29 Dec 2010 02:47:55 GMT

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

AbhijitBansode — Wed, 29 Dec 2010 06:20:09 GMT

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

johnw — Wed, 29 Dec 2010 18:11:17 GMT

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

AbhijitBansode — Thu, 30 Dec 2010 05:59:03 GMT

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

ACC_RCV_AMT

Cancellation_amount

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

johnw — Thu, 30 Dec 2010 23:30:18 GMT

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

AbhijitBansode — Fri, 31 Dec 2010 06:11:20 GMT

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?