Is it possible to group measures into Categories and Subcategories?

[qasim_00]

Hello everyone,

I'm relatively new to Qlik and I'm tasked with recreating a visual originally made in PowerBI. The visual is a matrix visualization where the Columns are Dates and each measure is grouped into Category and Subcategory, as can be seen with the image attached. Is there a way to achieve this in Qlik natively or would I need to perform some data augmentation or create a special view? 

Thanks for your help.

[Kushal_Chawda · Accepted solution]

@qasim_00  Yes, you can do that almost. You can change background colour if you are running Qlik sense version on Aug 2023 onwards. I am using cloud version, so I can change it. But apart from background colour, you can do other things like change background and text colour of dimension and measure values.

Create an Inline table for your Category and Sub Category Values. Change the Values according to your actual Category and Sub Category Values. I have used dummy data to show how it works

Load * Inline [
Category, Sub Category
M1, Measure1
M1, Measure2
M1, Measure3

M2, Measure4
M2, Measure5
M2, Measure6 ];

Create a pivot table with Row Dimension Category & Sub Category. Column Dimension as Month.

Use below Measure in Pivot table. I have used dummy measures, but you can use actual measure based on sub category

=pick(match(Category,'M1','M2'),

// Sub category Measures for M1
pick(match([Sub Category],'Measure1','Measure2','Measure3'),
sum(Measure1),
sum(Measure2),
sum(Measure3)),

// Sub category Measures for M2
pick(match([Sub Category],'Measure4','Measure5','Measure6'),
sum(Measure4),
sum(Measure5),
sum(Measure6)))

Change background colour of pivot table, go to pivot table properties -> Appearance -> Styling -> General, change background colour

Change background colour of Month dimension, For Month dimension background colour option, put below colour expression,

=rgb(137,31,133) -- purple shade

For all Dimensions (Category, Sub Category and Month), text colour expression should be =white()

For Measure background expression, use below expression

pick(match(Category,'M1','M2'),

// Sub category Measures for M1
pick(match([Sub Category],'Measure1','Measure2','Measure3'),
Black(),
Black(),
rgb(13,9,41)), // lighter shade of black to highlight rows

// Sub category Measures for M2
pick(match([Sub Category],'Measure4','Measure5','Measure6'),
rgb(13,9,41),
Black(),
Black()))

For Measure text colour expression, use below expression

pick(match(Category,'M1','M2'),

// Sub category Measures for M1
pick(match([Sub Category],'Measure1','Measure2','Measure3'),
white(),
white(),
rgb(137,31,133)), // purple shade to highlight measure values

// Sub category Measures for M2
pick(match([Sub Category],'Measure4','Measure5','Measure6'),
rgb(137,31,133),
white(),
white()))

Enable Show Total option for Month Dimension

[qasim_00]

Thank you so much! It worked as I needed. If possible could you also tell me how to get custom subtotals aswell for each subcategory? By custom I mean for come subcategories I would like the subtotal to show a complete SUM and for other subcategories I would like the subtotal to show a complete AVG.

[Kushal_Chawda · Accepted solution]

@qasim_00  you can create inline table with Total value in Sub category

Load * Inline [
Category, Sub Category
M1, Measure1
M1, Measure2
M1, Measure3
M1, Total

M2, Measure4
M2, Measure5
M2, Measure6
M2, Total];

Then you can place the expression for Total

=pick(match(Category,'M1','M2'),

// Sub category Measures for M1
pick(match([Sub Category],'Measure1','Measure2','Measure3','Total'),
sum(Measure1),
sum(Measure2),
sum(Measure3),
TotalExpression),

// Sub category Measures for M2a
pick(match([Sub Category],'Measure4','Measure5','Measure6','Total'),
sum(Measure4),
sum(Measure5),
sum(Measure6),
Total Expression))

[qasim_00]

Perfect. Thank you!