Using the same dimension for both axis in pivot crosstable

[juliekagergaard]

Hey QlikView Community

I'm trying to create a matrix where the two axis are the same field, and the calculation is of how similar each object is to another determined by a certain algorithm. I.e. something like:

	A	B	C
A	100%	30%	55%
B	30%	100%	70%
C	55%	70%	100%

But I cannot get each value to be equal to all values. This means I can only get it to evaluate down the middle diagonal (i.e. the 100% values). Like:

	A	B	C
A	100%	Missing	Missing
B	Missing	100%	Missing
C	Missing	Missing	100%

As the algorithm is determined by an aggregation over a set of attribute fields from another connected table, it is not possible to just create a alias field with the second names. How do I get A, B, and C to be an option of A, so that the A-B, A-C and so forth calculations are not just missing?

[juliekagergaard · Accepted solution]

Still not entirely it, but this helped me get it right!

I extracted my data so I have one field of equipment and then one field per area with the count of locations per equipment. Using crosstable I was then able to create a table with equipment, area and location count.

I saved this table in a temp qvd then loaded it in twice in qualified versions as table_1 and table_2 with equipment unqualified.

The pivot table then has the dimensions table_1.area and table_2.area, with the table_2.area field across.

The expression is then the following:

=  If( Table_1.area = Table_2.area, 100,  Num((Sum( Aggr((Sum(  Table_1.No_Of_Locs) + Sum(  Table_2.No_Of_Locs)) * (Count(  if( Table_1.No_Of_Locs <> 0, 1)) + Count(  if( Table_2.No_Of_Locs <> 0, 1))),  Table_1.area, Table_2.area, Equipment)))

/( Sum(Table_1.No_Of_Locs) + Sum(Table_2.No_Of_Locs) )

/ (Count( if( Table_1.No_Of_Locs <> 0, 1)) + Count( If( Table_2.No_Of_Locs <> 0, 1))) * 100, '##,00'))

As the algorithm cannot calculate the similarity between two similar areas (it will not end up being 100%), the if statement sets this value. The num() function helps limit the number of decimalpoints.