How to calculate varience%

[rohit214]

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

[CELAMBARASAN]

Hi,

     Did you tried with this?

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

Celambarasan

[rohit214]

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

[CELAMBARASAN]

Hi,

     Here how you arrived 6.13 as Variance %?.

     You mean it would be same for all?

Celambarasan

[CELAMBARASAN]

Hi,    

     Try something like this

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

Celambarasan

[rohit214]

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

[rohit214]

Hi

it is not working

thanks &regards

rohit

[CELAMBARASAN]

Hi,

     Above expression is not worked for you?

     Have you tried with aggr function?

Celambarasan

[CELAMBARASAN]

Hi,

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

Celambarasan

[rohit214]

hi

=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