topic Re: How to do calculations for such table in QlikView

How to do calculations for such table

Mon, 31 Dec 2012 08:35:07 GMT

Cleanliness	Doctors attitude	Nurse Attittude	Pharmacy Services	Mean A
75	20	35	100	(75+20+35+100) / 4
100	-	100	50	(100+100+50)/3
50	50	100	0	(50+50+100+0)/4
100	-	75	50	(100+75+50) / 4
=(75+100+50+100) / 4	(20+50) / 2	(35+100+100+75) / 4	(100+50+0+50)/4

When i generate the script i get the following table without last row and column.

1) How can i add column "Mean A" to the table??

2) What formula i can use to calculate the last raw values??

Re: How to do calculations for such table

sujeetsingh — Mon, 31 Dec 2012 09:43:16 GMT

plz explain .....in details with table structure

Re: How to do calculations for such table

Mon, 31 Dec 2012 09:54:04 GMT

what i mean how i get the caculation at end of each raw and each column

Re: How to do calculations for such table

Gysbert_Wassenaar — Mon, 31 Dec 2012 10:09:56 GMT

Use the avg() function for the first four metrics and rangeavg(Cleanliness,[Doctors attitude],[Nurse Attittude],[Pharmacy Services]) for Mean A. See attached qvw.

Re: How to do calculations for such table

Mon, 31 Dec 2012 11:03:39 GMT

This is great,

but say i have the script as follow:

[Survey]:

LOAD
Cleanliness,

Doctors attitude

Nurse Attittude,

Pharmacy Services

from table

and i want to add this formula to the script

rangeavg(Cleanliness,[Doctors attitude],[Nurse Attittude],[Pharmacy Services])

Re: How to do calculations for such table

Gysbert_Wassenaar — Mon, 31 Dec 2012 11:14:28 GMT

Just add it then

[Survey]:

LOAD
Cleanliness,

Doctors attitude

Nurse Attittude,

Pharmacy Services,

rangeavg(Cleanliness,[Doctors attitude],[Nurse Attittude],[Pharmacy Services]) as MeanA

from table

Re: How to do calculations for such table

Mon, 31 Dec 2012 11:28:45 GMT

OKKI Cool but what if the script was

Load

*,

ApplyMap('Satisfaction Score',[Overall Experience]) * Count as [Satisfaction Score],

ApplyMap('Admission Score',[Admission / registration process]) * Count as [Admission Score],

ApplyMap('Nursing Care Score',[Nurses Courtesy]) * Count as [Nursing Care Score],

rangeavg([Satisfaction Score],[Admission Score],[Nursing Care Score]) as MeanA

Resident Surveys;

It will give an error satisfcation score does not exist.

This is the idea i want to reach

Re: How to do calculations for such table

Gysbert_Wassenaar — Mon, 31 Dec 2012 11:36:41 GMT

Yes, you're creating new fields (like Satisfaction Score), but those fields don't exists yet as far as rangeavg knows. So you either need to use the same expressions in the rangeavg function (i.e. write them out) or use a precedent load so you can use the new fields:

SummaryTable:

load *, rangeavg([Satisfaction Score],[Admission Score],[Nursing Care Score]) as MeanA;

load *,
ApplyMap('Satisfaction Score',[Overall Experience]) * Count as [Satisfaction Score],
ApplyMap('Admission Score',[Admission / registration process]) * Count as [Admission Score],
ApplyMap('Nursing Care Score',[Nurses Courtesy]) * Count as [Nursing Care Score],
Resident Surveys;

Re: How to do calculations for such table

Tue, 01 Jan 2013 10:32:51 GMT

Thnx .

An extra question

the script generate table

column1 column2 column3

1 2 3

3 - 1

- 4 2

now i want to substitute the missing value with the average of each column its in, to get such table

column1 column2 column3

1 2 3

3 3 1

2 4 2