Complex cross-table representation

Mon, 19 Mar 2012 16:59:21 GMT

Good day,

I have to make a representation of a table that looks like a cross-table, but in more complex, because all lines don't use the same formula or are not in the same number format.

This is the result I want:

Where each line doesn't use the same formula:

"Category A : total" = "Sub-category A - 1" + "Sub-category A - 2"

"Category B : sub-total" = "Sub-category B - 1" + "Sub-category B - 2" + "Sub-category B - 3"

"Category B : total" = "Category B : sub-total" + "Sub-category B - 4"

"Sub-category C" is displayed in a different format.

Because I have tons of dimensions, I cannot pre-calculate the calculated fields in the loading script.

At first, I wanted to make a table that links the subcategory and its formula,

and use dynamicaly the formula with a syntax like

=$(=only(Formula))

Unfortunatly, this doesn't work, because (I guess) the "Subcategory" dimension is the one used in my chart, so the "Formula" dimension cannot be calculated for each line (same problem as the set analysis on dimensions used in the graphs).

So, the only solution I found is to make a huge "switch" statement that tests each possible values for the "Subcategory" dimension and provide the expected formula:

=pick(match(Subcategory, 'Sub-category A - 1', 'Sub-category A - 2', 'Category A : total', 'Sub-category B - 1', 'Sub-category B - 2', 'Sub-category B - 3', 'Category B : sub-total', 'Sub-category B - 4', 'Category B : total', 'Sub-category C') + 1,
'???',
num(sum([Sub-category A - 1]), '# ##0,00'),
num(sum([Sub-category A - 2]), '# ##0,00'),
num(sum([Sub-category A - 1]) + sum([Sub-category A - 2]), '# ##0,00'),
num(sum([Sub-category B - 1]), '# ##0,00'),
num(sum([Sub-category B - 2]), '# ##0,00'),
num(sum([Sub-category B - 3]), '# ##0,00'),
num(sum([Sub-category B - 1]) + sum([Sub-category B - 2]) + sum([Sub-category B - 3]), '# ##0,00'),
num(sum([Sub-category B - 4]), '# ##0,00'),
num(sum([Sub-category B - 1]) + sum([Sub-category B - 2]) + sum([Sub-category B - 3]) + sum([Sub-category B - 4]), '# ##0,00'),
num(sum([Sub-category C]), '# ##0,00%')
)

(this is the equivalent of many "if" interlinked)

This solution is a pain in the ¤¤¤...

Is there a better way ?

topic Complex cross-table representation in QlikView

Complex cross-table representation