Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Avg in a cross table

Hello,

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

I have the following data:

DIM 1 \ DIM 2XYTotal
A011
B101
Total112


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

DIM 1 \ DIM 2XYTotal
A0 %50 %50 %
B50 %0 %50 %
Total50 %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!

1 Solution

Accepted Solutions
Highlighted

Re: Avg in a cross table

Hi,

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

Hope it helps

Celambarasan

View solution in original post

8 Replies
Highlighted
Specialist
Specialist

Re: Avg in a cross table

Hi ,

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

Regards,

Iyyappan

Highlighted
Not applicable

Re: Avg in a cross table

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!

Highlighted

Re: Avg in a cross table

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

Highlighted
Not applicable

Re: Avg in a cross table

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

Highlighted
Not applicable

Re: Avg in a cross table

Thanks for your reply.

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

Dimension 1Dimension 2Amount
A X0
BY0
AY1
BX1

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 2XYTotal
A011
B101
Total112

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

Sorry for the misunderstanding.

Highlighted
Not applicable

Re: Avg in a cross table

Thats it! Thanks!

Highlighted

Re: Avg in a cross table

Hi,

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

Hope it helps

Celambarasan

View solution in original post

Highlighted
Not applicable

Re: Avg in a cross table

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