topic Re: Avg in a cross table in QlikView

Avg in a cross table

Thu, 09 Feb 2012 13:48:50 GMT

Hello,

I have a problem calculating the right averages in a cross table.

I have the following data:

DIM 1 \ DIM 2	X	Y	Total
A	0	1	1
B	1	0	1
Total	1	1	2

I would like to calculate averages based on the total of the whole table (total = 2). So, it must look like:

DIM 1 \ DIM 2	X	Y	Total
A	0 %	50 %	50 %
B	50 %	0 %	50 %
Total	50 %	50 %	100 %

I only get it work based on the row totals.

Searched the other discussions, but still no solution for my problem.

Can anybody help? Thanks!

Re: Avg in a cross table

v_iyyappan — Thu, 09 Feb 2012 14:54:20 GMT

Hi ,

Ignore the Last example . So check the Current attachment for solution.

Regards,

Iyyappan

Re: Avg in a cross table

Mon, 13 Feb 2012 07:51:14 GMT

Hi Iyyappan,

Thanks for the fast answer.

However, unfortunatly the file is opened too much times with a personal version of QV, so it cannot be opened.

Can you please send a screenshot of the solution?

Thanks!

Re: Avg in a cross table

CELAMBARASAN — Mon, 13 Feb 2012 08:28:20 GMT

Hi,

For X Expression use X/(Sum(TOTAL X)+Sum(TOTAL Y))

For Y Expression use Y/(Sum(TOTAL X)+Sum(TOTAL Y))

For Total Expression use RangeSum(X,Y)/ (Sum(TOTAL X)+Sum(TOTAL Y))

Don't forget check the Show in Percentage option in the number tab of the chart properties.

Hope it helps

Celambarasan

Re: Avg in a cross table

Mon, 13 Feb 2012 08:49:24 GMT

Hi,

Please find attached the complete solution for this issue. In the script, use the CROSSTABLE function in order to transform your input data! Do not import the total columns and rows from your input table! Then, you can build straight tables and other objects the usual way in your application. See example attached!

/sebablum

Re: Avg in a cross table

Mon, 13 Feb 2012 08:58:04 GMT

Thanks for your reply.

Maybe my first post was not realy clear, my original data is:

Dimension 1	Dimension 2	Amount
A	X	0
B	Y	0
A	Y	1
B	X	1

The first two columns (Dimension 1 and Dimension 2) are codes. The last column is the amount.

After using the pivot table, it is like:

DIM 1 \ DIM 2	X	Y	Total
A	0	1	1
B	1	0	1
Total	1	1	2

Now I am searching for the averages based on the overall total.

Sorry for the misunderstanding.

Re: Avg in a cross table

Mon, 13 Feb 2012 09:08:43 GMT

Thats it! Thanks!

Re: Avg in a cross table

CELAMBARASAN — Mon, 13 Feb 2012 09:09:58 GMT

Hi,

If so then use expression as Sum(Amount)/Sum(Total Amount)

Hope it helps

Celambarasan

Re: Avg in a cross table

Mon, 13 Feb 2012 09:30:35 GMT

I am not sure if I understood right, but please find a new example attached. In my opinion, Celambarasan's suggestion would work. I've used set analysis to solve the issue. In order to avoid rangesum, it is possible to create the total column as X+Y.

Solved?

/sebablum