Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Complex cross-table representation

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:

expected result2.png

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,

table.png

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 ?

0 Replies