topic Re: How to calculate varience% in QlikView

How to calculate varience%

rohit214 — Mon, 12 Mar 2012 07:02:04 GMT

Hi All ,

i have to calculate the varience with some condotion or logic,

1)if Q8-Q1 varience is -ve then i have to convert into positive value,

2) if Q8-Q1 varience is +ve or zero then convert it zero.

as shown in below.

ID	Customer Name	Dealer Name	Dealer Code	Region	Q1 rating	Q8 Rating
1	C1	D1	A1	XYZ	10	9
2	C2	D1	A1	XYZ	9	10
3	C3	D1	A1	XYZ	8	10
4	C4	D1	A1	XYZ	10	8
5	C5	D1	A1	XYZ	9	9

now My requirement is bsaed on below logic

Varience%=total(vaience)/total(Q8 rating) i.e 3/46=6.13%

and required format of able is

ID	Customer Name	Dealer Name	Dealer Code	Region	Q1 rating	Q8 Rating	Q8-Q1	Varience	Varience%
1	C1	D1	A1	XYZ	10	9	-1	1	6.13%
2	C2	D1	A1	XYZ	9	10	1	0	6.13%
3	C3	D1	A1	XYZ	8	10	2	0	6.13%
4	C4	D1	A1	XYZ	10	8	-2	2	6.13%
5	C5	D1	A1	XYZ	9	9	0	0	6.13%

thanks &regards

rohit

How to calculate varience%

CELAMBARASAN — Mon, 12 Mar 2012 07:10:20 GMT

Hi,

Did you tried with this?

Alt(if((Q8-Q1)<0,Fabs(Q8-Q1)),0)

Celambarasan

How to calculate varience%

rohit214 — Mon, 12 Mar 2012 07:28:04 GMT

iHi,

Celambarasan

I already tried this and i got the answer but not able to get Varience%

please suggest me how to calculate varience

thanks &regards

rohit

Re: How to calculate varience%

CELAMBARASAN — Mon, 12 Mar 2012 07:30:32 GMT

Hi,

Here how you arrived 6.13 as Variance %?.

You mean it would be same for all?

Celambarasan

Re: How to calculate varience%

CELAMBARASAN — Mon, 12 Mar 2012 07:34:16 GMT

Hi,

Try something like this

Sum(TOTAL aggr(Alt(if((Q8-Q1)<0,Fabs(Q8-Q1)),0),ID) )/Sum(TOTAL Q8)

Celambarasan

Re: How to calculate varience%

rohit214 — Mon, 12 Mar 2012 07:35:05 GMT

Hi ,

it comes from varience i.e mean total sum of varience/ total sum of Q8 rating

and that varience% i want in all rows for all customers

thanks&regards

rohit

Re: How to calculate varience%

rohit214 — Mon, 12 Mar 2012 08:43:03 GMT

it is not working

thanks &regards

rohit

How to calculate varience%

CELAMBARASAN — Mon, 12 Mar 2012 08:43:44 GMT

Hi,

Above expression is not worked for you?

Have you tried with aggr function?

Celambarasan

How to calculate varience%

CELAMBARASAN — Mon, 12 Mar 2012 08:44:59 GMT

Hi,

Can you post the expression and dimension you used in that chart?

Celambarasan

How to calculate varience%

rohit214 — Mon, 12 Mar 2012 09:08:37 GMT

=sum({<[CS Survey]={'Post Service Follow Up - 2012'},[Question No]={'Q1'},VIN_No_PSFU=p(VIN_No_SBC)>}ans) for Q1

Sum({<[CS Survey]={'SBC'},QuestionNo={'Q8'},VIN_No_SBC=p(VIN_No_PSFU)>}rat) For Q8,

For varience

=If(sum({<[CS Survey]={'SBC'},QuestionNo={'Q8'},VIN_No_SBC=p(VIN_No_PSFU)>} rat)-

sum({<[CS Survey]={'Post Service Follow Up - 2012'},[Question No]={'Q1'},VIN_No_PSFU=p(VIN_No_SBC)>}ans)<0,

Fabs(sum({<[CS Survey]={'SBC'},QuestionNo={'Q8'},VIN_No_SBC=p(VIN_No_PSFU)>} rat)-

sum({<[CS Survey]={'Post Service Follow Up - 2012'},[Question No]={'Q1'},VIN_No_PSFU=p(VIN_No_SBC)>}ans)),0)

Thanks & Regards

Rohit

How to calculate varience%

CELAMBARASAN — Mon, 12 Mar 2012 10:51:11 GMT

Hi,

Did you tried like this?.

Sum(TOTAL aggr(

If(sum({<[CS Survey]={'SBC'},QuestionNo={'Q8'},VIN_No_SBC=p(VIN_No_PSFU)>} rat)-

sum({<[CS Survey]={'Post Service Follow Up - 2012'},[Question No]={'Q1'},VIN_No_PSFU=p(VIN_No_SBC)>}ans)<0,

Fabs(sum({<[CS Survey]={'SBC'},QuestionNo={'Q8'},VIN_No_SBC=p(VIN_No_PSFU)>} rat)-

sum({<[CS Survey]={'Post Service Follow Up - 2012'},[Question No]={'Q1'},VIN_No_PSFU=p(VIN_No_SBC)>}ans)),0),ID

))/Sum(TOTAL {<[CS Survey]={'SBC'},QuestionNo={'Q8'},VIN_No_SBC=p(VIN_No_PSFU)>} rat)

Celambarasan

Re: How to calculate varience%

rohit214 — Mon, 12 Mar 2012 10:59:57 GMT

I tried but it shows Q1-Q8/Q8 rating for all customer which is wrong answer

but i want varience/Q8 rating in that example values should be 3/46

thanks &regards

rohit

How to calculate varience%

rohit214 — Mon, 12 Mar 2012 13:05:09 GMT

Hi all please suggest me.

how to get sum ( total varience)/sum(Total Q8 Rating)

for reference please take alook in tables

thanks & regards

rohit

How to calculate varience%

Fri, 16 Mar 2012 05:52:56 GMT

Hi Rohit....
I am looking for my better future in IT field.
I hv 6 months experience in QlikView with good knowledge of Server Administration, except (Set Analysis and macros).....

If any job related to Qlikview is in your knowledge , Pllzzzz inform me on my id

Gaurav99choudhary@gmail.com

Thank you
Gaurav Choudhary(India)

How to calculate varience%

rohit214 — Sun, 18 Mar 2012 17:12:29 GMT

hi gaurav,

send me your detail in rht214@hmail.com

if i can do anything for you that will be my pleasure

thanks& regards

rohit

ID	Customer Name	Dealer Name	Dealer Code	Region	Q1 rating	Q8 Rating
1	C1	D1	A1	XYZ	10	9
2	C2	D1	A1	XYZ	9	10
3	C3	D1	A1	XYZ	8	10
4	C4	D1	A1	XYZ	10	8
5	C5	D1	A1	XYZ	9	9

ID	Customer Name	Dealer Name	Dealer Code	Region	Q1 rating	Q8 Rating
1	C1	D1	A1	XYZ	10	9
2	C2	D1	A1	XYZ	9	10
3	C3	D1	A1	XYZ	8	10
4	C4	D1	A1	XYZ	10	8
5	C5	D1	A1	XYZ	9	9

ID	Customer Name	Dealer Name	Dealer Code	Region	Q1 rating	Q8 Rating
1	C1	D1	A1	XYZ	10	9
2	C2	D1	A1	XYZ	9	10
3	C3	D1	A1	XYZ	8	10
4	C4	D1	A1	XYZ	10	8
5	C5	D1	A1	XYZ	9	9