topic How to Calculate in chart in QlikView

How to Calculate in chart

Anonymous — Wed, 15 Dec 2010 09:09:26 GMT

Hi all,

Can any one help me to do the below calulation in the chart. here i have to find the %Rate. this calculation i have to do in the chart itself

i have attached an excel having the sample data and calculation

Version No	Year	Month	Prod_Vol	Upg_Vol	Cal
1.0	2010	Jan	100	40
1.0	2010	Feb	115	45
1.0	2010	Mar	120	50
1.0	2010	Apr	125	55
1.0	2010	May	130	60
2.0	2010	Jan	111	41	41%
2.0	2010	Feb	116	46	40%
2.0	2010	Mar	121	51	43%
2.0	2010	Apr	126	56	45%
2.0	2010	May	131	61	47%
3.0	2010	Jan	112	142	67%
3.0	2010	Feb	117	147	64%
3.0	2010	Mar	122	152	63%
3.0	2010	Apr	127	157	63%
3.0	2010	May	132	162	62%
4.0	2010	Jan	113	243	75%
4.0	2010	Feb	118	248	71%
4.0	2010	Mar	123	253	70%
4.0	2010	Apr	128	258	68%
4.0	2010	May	133	263	67%

Calculation logic:

1. No need to calculateh the rate for the lowest version

2. to calculate the rate for version 2.0 use the below formula

Rate 2.0 jan = Upg_vol 2.0 Jan / Prod_Vol 1.0 Jan
Rate 2.0 Feb = Upg_vol 2.0 Feb / Prod_Vol 1.0 feb
Rate 2.0 mar = Upg_vol 2.0 mar / Prod_Vol 1.0 mar
Rate 2.0 Apr = Upg_vol 2.0 apr / Prod_Vol 1.0 apr
Rate 2.0 May = Upg_vol 2.0 may / Prod_Vol 1.0 may

3 to calculate the rate for version 3.0 use the below formula

Rate 3.0 jan = Upg_vol 3.0 Jan / (Prod_Vol 1.0 Jan + Prod_Vol 2.0 Jan)
Rate 3.0 Feb = Upg_vol 3.0 Feb / (Prod_Vol 1.0 feb + Prod_Vol 2.0 feb)
Rate 3.0 mar = Upg_vol 3.0 mar / (Prod_Vol 1.0 mar + Prod_Vol 2.0 mar)
Rate 3.0 Apr = Upg_vol 3.0 apr / (Prod_Vol 1.0 apr + Prod_Vol 2.0 apr)
Rate 3.0 May = Upg_vol 3.0 may / (Prod_Vol 1.0 may + Prod_Vol 2.0 may)

4 to calculate the rate for version 4.0 use the below formula

Rate 4.0 jan = Upg_vol 4.0 Jan / (Prod_Vol 1.0 Jan + Prod_Vol 2.0 Jan + Prod_Vol 3.0 Jan )
Rate 4.0 Feb = Upg_vol 4.0 Feb / (Prod_Vol 1.0 feb + Prod_Vol 2.0 feb + Prod_Vol 3.0 Feb)
Rate 4.0 mar = Upg_vol 4.0 mar / (Prod_Vol 1.0 mar + Prod_Vol 2.0 mar + Prod_Vol 3.0 Mar)
Rate 4.0 Apr = Upg_vol 4.0 apr / (Prod_Vol 1.0 apr + Prod_Vol 2.0 apr + Prod_Vol 3.0 Apr)
Rate 4.0 May = Upg_vol 4.0 may / (Prod_Vol 1.0 may + Prod_Vol 2.0 may + Prod_Vol 3.0 May)

Here version may be extended in furture so i cant give the version no in the chart

I hope you ll understand the above calculation. please suggest me how to calculate in chart

i have attached an excel for your reference.

How to Calculate in chart

lironbaram — Wed, 15 Dec 2010 13:50:52 GMT

hei

attach is an example

basically you'll need to load reference table which hold rows for for every version for every month for every year

this is not a great way , in term of performance but i don't see other way

give it a try

hope it helps you

How to Calculate in chart

Anonymous — Wed, 15 Dec 2010 16:45:24 GMT

hi yes i tried this but its takes too much time to calculate the value, performace of the chart got reduced, can u suggest any other method for this

How to Calculate in chart

Anonymous — Thu, 16 Dec 2010 07:18:37 GMT

hi,

Is any one having the solution for the above calculation. please suggest me an idea for this

How to Calculate in chart

maneshkhottcpl — Thu, 16 Dec 2010 07:53:44 GMT

Hi,

U can perform the calculation in backend script, it increases the reload time but improve the chart performance.

If u are unable to do, send me the application.

🙂

How to Calculate in chart

spsrk_84 — Thu, 16 Dec 2010 08:54:03 GMT

Hi Manesh,

i got the solution in script level but only thing is you need to change the sort order in script .Go through the code and copy the code to your file and reload, perhaps both the Qvw and Excel file should exists in same folder.In UI Part u just the pivot chart to display values

In Pivot take all the Dimensions and in the expression just do Sum(Upd_Vol/NewProd value)

Copy the code from text file and test it with the excel data then apply the same logic to your actual data.

Hope if you follow in this way i don't think it will create any problems since the temp tables are dropped at the end.

Regards,

Ajay